CODEWITHSUNDEEP
sqlrollup
user2096512
user2096512

Reputation: 469

SQL: Rollup without grouping

I'm trying to add subtotals to the bottom of my output to this code using ROLLUP, but it fails saying that they are not in a group. I don't want them grouped, I just want the subtotals at the bottom.

SELECT Date, HomeTeam, AwayTeam, FTHG, FTAG, FTR, 
CASE WHEN HomeTeam = 'Arsenal' THEN
CASE FTR WHEN 'H' THEN 3 WHEN 'D' THEN 1 WHEN 'A' THEN 0 ELSE 0 END
WHEN AwayTeam = 'Arsenal' THEN
CASE FTR WHEN 'H' THEN 0 WHEN 'D' THEN 1 WHEN 'A' THEN 3 ELSE 0 END
ELSE 0
END
AS Points, CASE WHEN HomeTeam = 'Arsenal' THEN FTHG - FTAG
            WHEN AwayTeam = 'Arsenal' THEN FTAG - FTHG
            ELSE 0
            END AS GD
FROM [Games].[dbo].[Master]
WHERE (HomeTeam = 'Arsenal' OR AwayTeam = 'Arsenal') AND Date < '20121201'
ORDER BY Date DESC

So id like the output as normal but with a row at the bottom, with HomeTeam = "Total", Points = (sum of points column), GD = (sum of GD column).

Date                     HomeTeam         AwayTeam     FTHG  FTAG  FTR  Points GD
-----------------------  ---------------  -----------  ----  ----  ---  ------ --
2012-11-28 00:00:00.000  Everton          Arsenal      1     1     D    1      0
2012-11-24 00:00:00.000  Aston Villa      Arsenal      0     0     D    1      0
2012-11-17 00:00:00.000  Arsenal          Tottenham    5     2     H    3      3
2012-11-10 00:00:00.000  Arsenal          Fulham       3     3     D    1      0
2012-11-03 00:00:00.000  Man Utd          Arsenal      2     1     H    0      -1
2012-10-27 00:00:00.000  Arsenal          QPR          1     0     H    3      1
2012-10-20 00:00:00.000  Norwich City     Arsenal      1     0     H    0      -1
2012-10-06 00:00:00.000  West Ham United  Arsenal      1     3     A    3      2
2012-09-29 00:00:00.000  Arsenal          Chelsea      1     2     A    0      -1
2012-09-23 00:00:00.000  Manchester City  Arsenal      1     1     D    1      0
2012-09-15 00:00:00.000  Arsenal          Southampton  6     1     H    3      5
2012-09-02 00:00:00.000  Liverpool        Arsenal      0     2     A    3      2
2012-08-26 00:00:00.000  Stoke City       Arsenal      0     0     D    1      0
2012-08-18 00:00:00.000  Arsenal          Sunderland   0     0     D    1      0

Is it possible?

Upvotes: 2

Views: 5360

Answers (2)

Taryn
Taryn

Reputation: 247850

You did not state what version of SQL Server you are using but if you are using SQL Server 2008+, then you should be able to accomplish this using GROUPING SETS to get the rolled up row:

;with cte as
(
  SELECT Date, 
    HomeTeam, 
    AwayTeam, 
    FTHG, 
    FTAG, 
    FTR, 
    CASE 
      WHEN HomeTeam = 'Arsenal' 
        THEN
          CASE FTR 
            WHEN 'H' THEN 3 
            WHEN 'D' THEN 1 
            WHEN 'A' THEN 0 
            ELSE 0 END
      WHEN AwayTeam = 'Arsenal' 
        THEN
          CASE FTR 
            WHEN 'H' THEN 0 
            WHEN 'D' THEN 1 
            WHEN 'A' 
            THEN 3 
            ELSE 0 END
      ELSE 0
    END AS Points, 
    CASE 
      WHEN HomeTeam = 'Arsenal' THEN FTHG - FTAG
      WHEN AwayTeam = 'Arsenal' THEN FTAG - FTHG
      ELSE 0
    END AS GD
  FROM [Games].[dbo].[Master] 
  WHERE (HomeTeam = 'Arsenal' OR AwayTeam = 'Arsenal') AND Date < '20121201'
)
select 
  coalesce(convert(char(10), date, 120), 'Total')date, 
  coalesce(hometeam, '') hometeam, 
  coalesce(awayteam , '') awayteam, 
  sum(fthg) fthg, 
  sum(ftag) ftag,
  coalesce(ftr, '') ftr,
  sum(points) points,
  sum(gd) gd
from cte
group by grouping sets((date, hometeam, awayteam, ftr), ())

See SQL Fiddle with Demo.

If you are running SQL Server 2005+, then you can also use ROLLUP():

;with cte as
(
  SELECT Date, 
    HomeTeam, 
    AwayTeam, 
    FTHG, 
    FTAG, 
    FTR, 
    CASE 
      WHEN HomeTeam = 'Arsenal' 
        THEN
          CASE FTR 
            WHEN 'H' THEN 3 
            WHEN 'D' THEN 1 
            WHEN 'A' THEN 0 
            ELSE 0 END
      WHEN AwayTeam = 'Arsenal' 
        THEN
          CASE FTR 
            WHEN 'H' THEN 0 
            WHEN 'D' THEN 1 
            WHEN 'A' 
            THEN 3 
            ELSE 0 END
      ELSE 0
    END AS Points, 
    CASE 
      WHEN HomeTeam = 'Arsenal' THEN FTHG - FTAG
      WHEN AwayTeam = 'Arsenal' THEN FTAG - FTHG
      ELSE 0
    END AS GD
  FROM games
  WHERE (HomeTeam = 'Arsenal' OR AwayTeam = 'Arsenal') AND Date < '20121201'
)
select 
  coalesce(convert(char(10), date, 120), 'Total')date, 
  coalesce(hometeam, '') hometeam, 
  coalesce(awayteam , '') awayteam, 
  sum(fthg) fthg, 
  sum(ftag) ftag,
  coalesce(ftr, '') ftr,
  sum(points) points,
  sum(gd) gd
from cte
group by rollup((date, hometeam, awayteam, ftr))

See SQL Fiddle with Demo

Upvotes: 2

Andriy M
Andriy M

Reputation: 77717

You could use CROSS APPLY to calculate Points and GD. That way you would be able to reference the results in the same SELECT:

SELECT
  m.Date,
  m.HomeTeam,
  m.AwayTeam,
  m.FTHG,
  m.FTAG,
  m.FTR,
  x.Points,
  x.GD,
  TotalPoints = SUM(x.Points) OVER (),
  TotalGD     = SUM(x.GD) OVER ()
FROM [Games].[dbo].[Master] AS m
CROSS APPLY (
  SELECT
    Points = CASE
      WHEN m.FTR = 'D' THEN 1
      WHEN m.FTR = 'H' AND m.HomeTeam = 'Arsenal'
        OR m.FTR = 'A' AND m.AwayTeam = 'Arsenal' THEN 3
      ELSE 0
    END,
    GD = CASE m.HomeTeam
      WHEN 'Arsenal'
      THEN m.FTHG - m.FTAG
      ELSE m.FTAG - m.FTHG
    END
) AS x
WHERE (m.HomeTeam = 'Arsenal' OR m.AwayTeam = 'Arsenal') AND m.Date < '20121201'
ORDER BY m.Date DESC
;

Upvotes: 3

Related Questions