CODEWITHSUNDEEP
sqlt-sql
DaFunkyAlex
DaFunkyAlex

Reputation: 1969

row counter with condition in two different columns

I have the following tables with sport results (e.g. football):

tblGoals (RowId, GameRowIdm PlayerRowId, TeamRowId, GoalMinute)

RowId | GameRowId | PlayerRowId | TeamRowId | GoalMinute
--------------------------------------------------------
1     | 1         | 1           | 1         | 25
2     | 1         | 2           | 2         | 45
3     | 1         | 3           | 1         | 66

tblPlayers (RowId, PlayerName)

RowId | PlayerName
------------------
1     | John Snow
2     | Frank Underwood
3     | Jack Bauer

tblGames (RowId, TeamHomeRowId, TeamGuestRowId)

RowId | TeamHomeRowId | TeamGuestRowId | GameDate
---------------------------------------------------
1     | 1             | 2              | 2015-01-01

Now I want get a list of all goals. The list should look like this:

GoalMinute | PlayerName      | GoalsHome | GoalsGuest
-----------------------------------------------------
25         | John Snow       | 1         | 0
45         | Frank Underwood | 1         | 1
66         | Jack Bauer      | 2         | 1

GoalsHome and GoalsGuest should be a counter of the shot goals for the team. So e.g. if you check the last row, the result is 2:1 for home team. To get this list of goals, I used this statement:

SELECT  t_gol.GoalMinute,
        t_ply.PlayerName,
        CASE WHEN
            t_gol.TeamRowId = t_gam.TeamHomeRowId 
            THEN ROW_NUMBER()  OVER (PARTITION BY t_gam.TeamHomeRowId ORDER BY t_gam.TeamHomeRowId)
        END AS GoalsHome,
        CASE WHEN
            t_gol.TeamRowId = t_gam.TeamGuestRowId 
            THEN ROW_NUMBER()  OVER (PARTITION BY t_gam.TeamGuestRowId ORDER BY t_gam.TeamGuestRowId)
        END AS GoalsGuest
FROM    dbo.tblGoalsFussball AS t_gol
LEFT JOIN dbo.tblPlayersFussball AS t_ply ON (t_ply.RowId = t_gol.PlayerRowId)
LEFT JOIN dbo.tblGames AS t_gam ON (t_gam.RowId = t_gol.GameRowId)
WHERE   t_gol.GameRowId = @match_row

But what I get is this here:

GoalMinute | PlayerName      | GoalsHome | GoalsGuest
-----------------------------------------------------
25         | John Snow       | 1         | NULL
45         | Frank Underwood | NULL      | 2
66         | Jack Bauer      | 3         | NULL

Maybe ROW_NUMBER() is the wrong approach?

Upvotes: 0

Views: 92

Answers (2)

Gane
Gane

Reputation: 120

I think the easiest way is with subqueries..

SELECT 
  tgs.GoalMinute,
  tpl.PlayerName,
  ( SELECT 
        COUNT(t.RowId) 
    FROM 
        tblgoals AS t 
    WHERE t.GoalMinute <= tgs.GoalMinute 
        AND t.GameRowId = tgm.RowId
        AND t.TeamRowId = tgm.TeamHomeRowId
  ) AS HomeGoals,
  ( SELECT 
        COUNT(t.RowId) 
    FROM 
        tblgoals AS t 
    WHERE t.GoalMinute <= tgs.GoalMinute 
        AND t.GameRowId = tgm.RowId
        AND t.TeamRowId = tgm.TeamGuestRowId
  ) AS GuestGoals
FROM 
     tblgoals AS tgs
     JOIN tblplayers AS tpl ON tgs.RowId = tpl.RowId
     JOIN tblGames AS tgm ON tgm.RowId = tgs.GameRowId
ORDER BY tgs.GoalMinute

Upvotes: 1

jpw
jpw

Reputation: 44881

I would do the running total using sum() as a windowed aggregate function with the over ... clause, which works in SQL Server 2012+. 

select 
    g.RowId, g.GameDate, t.GoalMinute, p.PlayerName, 
    GoalsHome = COALESCE(SUM(case when TeamRowId = g.TeamHomeRowId then 1 end) OVER (PARTITION BY gamerowid ORDER BY goalminute),0),
    GoalsGuest = COALESCE(SUM(case when TeamRowId = g.TeamGuestRowId then 1 end) OVER (PARTITION BY gamerowid ORDER BY goalminute),0) 
from tblGoals t
join tblPlayers p on t.PlayerRowId = p.RowId
join tblGames g on t.GameRowId = g.RowId
order by t.GameRowId, t.GoalMinute

Another approach (that also works in older versions) is to use a self-join and sum up the rows with lower goalminutes. For ease of reading I've used a common table expression to split the goals into two columns for home and guest team:

;with t as (
    select 
       g.GoalMinute, g.PlayerRowId, g.GameRowId, 
       case when TeamRowId = ga.TeamHomeRowId then 1 end HomeGoals,
       case when TeamRowId = ga.TeamGuestRowId then 1 end GuestGoals
    from tblGoals g
    join tblGames ga on g.GameRowId = ga.RowId
)

select 
    g.RowId, g.GameDate, t.GoalMinute, p.PlayerName, 
    GoalsHome  = (select sum(coalesce(HomeGoals,0)) from t t2 where t2.GoalMinute <= t.GoalMinute and t2.GameRowId = t.GameRowId),
    GoalsGuest = (select sum(coalesce(GuestGoals,0)) from t t2 where t2.GoalMinute <= t.GoalMinute and t2.GameRowId = t.GameRowId)
from t
join tblPlayers p on t.PlayerRowId = p.RowId
join tblGames g on t.GameRowId = g.RowId
order by t.GameRowId, t.GoalMinute

The CTE isn't necessary though, you could just as well use a derived table

Sample SQL Fiddle

Upvotes: 1

Related Questions