count if and where (1 Viewer)

lordrom1 · Nov 15, 2017

i'm looking to build a query that will give me a count of

away team (h=a) corners (tp=c) where the time (t_val) if greater than 0, 1, 2, 3 minute etc for each id.

So i get a count of how many corners etc are yet to be scored in the game.

so id 67343499 away corners >45 (ie second half) = 2

events id tp h t t_val
67343499 c a 90+2 90
67343499 c a 86 86
67343499 g h 72 72
67343499 g h 55 55
67343499 c h 37 37
67343499 c h 28 28
67343499 c h 24 24
67343499 c h 16 16
67343499 c h 6 6
67343499 c h 4 4
67343476 c h 64 64
67343476 c h 63 63
67343476 c a 62 62
67343476 g a 60 60
67343476 c a 56 56
67343476 c a 55 55
67343476 c a 36 36
67343476 c a 35 35
67343476 c a 22 22
67343476 c a 19 19
67343476 g h 9 9
67343476 g a 7 7
67343476 g h 2 2
67343476 c h 2 2
67343476 c h 1 1
67343479 c a 90+10 90
67343479 c h 90+9 90
67343479 c h 90+8 90
67343479 c a 90+1 90
67343479 c h 81 81
67343479 c a 71 71
67343479 c h 70 70
67343479 c h 69 69
67343479 g a 68 68
67343479 g h 61 61
67343479 c h 61 61
67343479 c a 52 52
67343479 c a 39 39
67343479 c a 38 38
67343479 c h 19 19
67343479 c h 17 17
67343479 g h 10 10
67343479 c h 5 5
67343481 c a 73 73
67343481 c a 70 70
67343481 c h 63 63
67343481 c a 61 61
67343481 c a 60 60
67343481 g h 47 47
67343481 c h 41 41
67343481 c a 38 38
67343481 c h 34 34
67343481 c h 31 31
67343481 c h 27 27
67343481 c h 22 22
67343481 c a 20 20

plog · Nov 15, 2017

... if greater than 0, 1, 2, 3 minute etc for each id.

You lost me with the etc part. Either you have solid criteria or you don't. I don't fully understand what results you are looking for.

Using the data you posted, could you show what data you expect your query to return?

lordrom1 · Nov 16, 2017

67343499 c a 90+2 90
67343499 c a 86 86
67343499 g h 72 72
67343499 g h 55 55
67343499 c h 37 37
67343499 c h 28 28
67343499 c h 24 24
67343499 c h 16 16
67343499 c h 6 6
67343499 c h 4 4

etc referred to all minutes from 0 to 90

so for the above game i want to know how many home goals, away goals, home corners and away corners remain to be scored at any minute in a match.

so on zero minutes it would count:-
home goals = 2
away goals = 0
home corners = 6
away corners = 2

after 1 minute it would return the same counts since none of the \above events happened in the 1st minute.

after 10 minutes 2 home corners have occurred, so it would return

home goals = 2
away goals = 0
home corners = 4
away corners = 2

in terms of output i guess i'm looking for something like this

match id, minute, hg, ag, hc, ac
67343499 ,0,x,x,x,x
67343499 ,1,x,x,x,x
67343499 ,2,x,x,x,x
...
67343499 ,89,x,x,x,x
67343500,0,x,x,x,x

i hope that clarifies it

thanks

lordrom1 · Nov 16, 2017

hi plog, I have responded but no idea why it's not on site. will re-reply later.

isladogs · Nov 16, 2017

As you have less than 10 posts, only zip files can be attached to posts
Yours may be lost awaiting moderator approval
Suggest re-post & zip it.

lordrom1 · Nov 16, 2017

the query is meant to let me know how many of each event type are left to occur in the game after each minute. so in the above example 67343499 the away corners occurred in the 86th and 90th minutes. so it would return 2 for minutes 0 to 85 (since 2 corners are still to occur), 1 from 86 to 89 (since 1 corner is yet to occur) and 0 for 90 since no more corners occur.

for home corners, there were 6 in total, so for minutes 0 to 3 it will return 6, but minute 4 will return 5 since 1 has occured by then and 5 still to occur.

i'd like the return to be:-

matchId, minute, homecorners, awaycorners, homegoals, awaygoals
67343499,0,x,x,x,x
67343499,1,x,x,x,x
67343499,2,x,x,x,x
...
67343499,90,x,x,x,x
67343500,0,x,x,x,x
67343500,1,x,x,x,x

hope that makes better sense.

lordrom1 · Nov 16, 2017

ridders said:
As you have less than 10 posts, only zip files can be attached to posts
Yours may be lost awaiting moderator approval
Suggest re-post & zip it.

thanks, retyped it without the table

plog · Nov 16, 2017

First this is going to be a bear to run. Second, you are going to need a table with every minute you want to report on:

GameMinutes
gameMinute
1
2
3
...
90

Then, this is the outline of the query:

Code:

SELECT matchID, gameMinute, SUM(IIF(t_val<=gameMinute AND h="a" AND tp="c", 1,0) AS AwayCorners 
FROM YourTableNameHere, GameMinutes
GROUP BY  matchID, gameMinute

Replace "YourTableNameHere" with the name of your table and that will give you the away corners for each minute for each matchID. To get all the other fields copy that big SUM field and replace the logic with the correct one.

lordrom1 · Nov 17, 2017

many thanks. not quick as you say, but only needs running once in a while when new data added.

much appreciated

count if and where (1 Viewer)

lordrom1

Registered User.

plog

Banishment Pending

lordrom1

Registered User.

lordrom1

Registered User.

isladogs

MVP / VIP

lordrom1

Registered User.

lordrom1

Registered User.

plog

Banishment Pending

lordrom1

Registered User.

Similar threads

Users who are viewing this thread