count if and where

[lordrom1]

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]

... 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]

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]

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

 

[isladogs]

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]

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]

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]

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:

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]

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

much appreciated