CODEWITHSUNDEEP
sqlsql-server
Elixir
Elixir

Reputation: 303

SQL Grand Total last ROW

Is there a way I could get the grand total of each month. I've looked into Rollup but cant seem to figure it out.

Query I have is

SELECT t.city,f.fname,f.lname,
SUM(CASE datepart(month,ddate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
SUM(CASE datepart(month,ddate) WHEN 2 THEN 1 ELSE 0 END) AS 'February',
SUM(CASE datepart(month,ddate) WHEN 3 THEN 1 ELSE 0 END) AS 'March',
SUM(CASE datepart(month,ddate) WHEN 4 THEN 1 ELSE 0 END) AS 'April',
SUM(CASE datepart(month,ddate) WHEN 5 THEN 1 ELSE 0 END) AS 'May',
SUM(CASE datepart(month,ddate) WHEN 6 THEN 1 ELSE 0 END) AS 'June',
SUM(CASE datepart(month,ddate) WHEN 7 THEN 1 ELSE 0 END) AS 'July',
SUM(CASE datepart(month,ddate) WHEN 8 THEN 1 ELSE 0 END) AS 'August',
SUM(CASE datepart(month,ddate) WHEN 9 THEN 1 ELSE 0 END) AS 'September',
SUM(CASE datepart(month,ddate) WHEN 10 THEN 1 ELSE 0 END) AS 'October',
SUM(CASE datepart(month,ddate) WHEN 11 THEN 1 ELSE 0 END) AS 'November',
SUM(CASE datepart(month,ddate) WHEN 12 THEN 1 ELSE 0 END) AS 'December',
SUM(CASE datepart(year,ddate) WHEN 2014 THEN 1 ELSE 0 END) AS 'TOTAL'
from  world T 
INNER JOIN sales F  ON T.ID=F.ID
where t.city = ROME 
group by t.city,f.fname,f.lname

Output example

t.city,       f.fname,   f.lname    January  total
ROME           John       Doe         5       5 
Grand Total                           5       5

Upvotes: 1

Views: 750

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Did you try this?

group by GROUPING SETS((t.city, f.fname, f.lname), ())

To get Grand Total you'll need to change the select as well.

And, as a note: use single quotes only for string and date constants. Using them for column identifiers can lead to confusing and problems. Either drop the quotes altogether or use square braces or double quotes.

EDIT:

Without group by extensions or CTEs this is a pain. There is a trick to doing it with minimal modifications:

SELECT (case when which = 'normal' then t.city else 'Grand Total' end),
       (case when which = 'normal' then f.fname end),
       (case when which = 'normal' then f.lname end),
SUM(CASE datepart(month,ddate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
SUM(CASE datepart(month,ddate) WHEN 2 THEN 1 ELSE 0 END) AS 'February',
SUM(CASE datepart(month,ddate) WHEN 3 THEN 1 ELSE 0 END) AS 'March',
SUM(CASE datepart(month,ddate) WHEN 4 THEN 1 ELSE 0 END) AS 'April',
SUM(CASE datepart(month,ddate) WHEN 5 THEN 1 ELSE 0 END) AS 'May',
SUM(CASE datepart(month,ddate) WHEN 6 THEN 1 ELSE 0 END) AS 'June',
SUM(CASE datepart(month,ddate) WHEN 7 THEN 1 ELSE 0 END) AS 'July',
SUM(CASE datepart(month,ddate) WHEN 8 THEN 1 ELSE 0 END) AS 'August',
SUM(CASE datepart(month,ddate) WHEN 9 THEN 1 ELSE 0 END) AS 'September',
SUM(CASE datepart(month,ddate) WHEN 10 THEN 1 ELSE 0 END) AS 'October',
SUM(CASE datepart(month,ddate) WHEN 11 THEN 1 ELSE 0 END) AS 'November',
SUM(CASE datepart(month,ddate) WHEN 12 THEN 1 ELSE 0 END) AS 'December',
SUM(CASE datepart(year,ddate) WHEN 2014 THEN 1 ELSE 0 END) AS 'TOTAL'
from  world T INNER JOIN
      sales F
      ON T.ID=F.ID cross join
      (select 'normal' as which union all select 'total') as which
where t.city = ROME 
group by (case when which = 'normal' then t.city else 'Grand Total' end),
         (case when which = 'normal' then f.fname end),
         (case when which = 'normal' then f.lname end);

(I'm not reformatting the rest of the query, but you should not use single quotes for column identifiers. Only use single quotes for string and date constants.)

Upvotes: 2

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

Edit: Using GROUP BY ROLLUP(grouping columns) this will calculate the sum of the aggregated columns. By default it will return 'NULL' for all grouped columns but you can put an ISNULL wrapper around to get rid of it, or return a specific value.

SELECT ISNULL(t.city, 'Grand Total') AS [City],f.fname AS [Fname],f.lname AS [Lname],
  SUM(CASE datepart(month,ddate) WHEN 1 THEN 1 ELSE 0 END) AS [January],
  SUM(CASE datepart(month,ddate) WHEN 2 THEN 1 ELSE 0 END) AS [February],
  SUM(CASE datepart(month,ddate) WHEN 3 THEN 1 ELSE 0 END) AS [March],
  SUM(CASE datepart(month,ddate) WHEN 4 THEN 1 ELSE 0 END) AS [April],
  SUM(CASE datepart(month,ddate) WHEN 5 THEN 1 ELSE 0 END) AS [May],
  SUM(CASE datepart(month,ddate) WHEN 6 THEN 1 ELSE 0 END) AS [June],
  SUM(CASE datepart(month,ddate) WHEN 7 THEN 1 ELSE 0 END) AS [July],
  SUM(CASE datepart(month,ddate) WHEN 8 THEN 1 ELSE 0 END) AS [August],
  SUM(CASE datepart(month,ddate) WHEN 9 THEN 1 ELSE 0 END) AS [September],
  SUM(CASE datepart(month,ddate) WHEN 10 THEN 1 ELSE 0 END) AS [October],
  SUM(CASE datepart(month,ddate) WHEN 11 THEN 1 ELSE 0 END) AS [November],
  SUM(CASE datepart(month,ddate) WHEN 12 THEN 1 ELSE 0 END) AS [December],
  SUM(CASE datepart(year,ddate) WHEN 2014 THEN 1 ELSE 0 END) AS [TOTAL]
FROM  world T 
   INNER JOIN sales F  ON T.ID=F.ID
WHERE t.city = ROME 
GROUP BY ROLLUP(t.city,f.fname,f.lname)

Upvotes: 0

Related Questions