I am running the query below and need to include a total at the bottom but not sure how to run it in SQL Server. I can copy the data and do an auto sum in Excel but it's a bit repetitive. How can I achieve this?
SELECT
LOCATION,
SUM(CASE WHEN my_date >= '10/1/2009' AND my_date <'01/01/2010' THEN QTY
ELSE NULL END) AS QTR1,
SUM(CASE WHEN my_date >= '01/1/2010' AND my_date <'04/01/2010' THEN QTY
ELSE NULL END) AS QTR2,
SUM(CASE WHEN my_date >= '04/1/2010' AND my_date <'07/01/2010' THEN QTY
ELSE NULL END) AS QTR3,
SUM(CASE WHEN my_date >= '07/1/2010' AND my_date <'10/01/2010' THEN QTY
ELSE NULL END) AS QTR4
FROM MYTABLE
GROUP BY location
Location QTR1 QTR2 QTR3 QTR4
FL 171 174 99 177
MD 662 640 422 497
VA 2,713 4,454 3,062 2,793
NY 479 380 227 477
PA 7,197 10,078 5,625 6,448
NJ 2,626 3,848 2,428 2,089
TOTAL ??? ??? ??? ???
Does GROUP BY LOCATION WITH ROLLUP do what you need?
;WITH MYTABLE AS
(
SELECT 'FL' AS LOCATION,CAST('10/1/2009' AS DATETIME) AS my_date, 1 AS QTY UNION ALL
SELECT 'MD' AS LOCATION,'10/1/2009' AS my_date, 27 AS QTY UNION ALL
SELECT 'MD' AS LOCATION,'01/1/2010' AS my_date, 1024 AS QTY UNION ALL
SELECT 'FL' AS LOCATION,'07/1/2010' AS my_date, 98 AS QTY UNION ALL
SELECT 'FL' AS LOCATION,'07/1/2010' AS my_date, 5 AS QTY
)
SELECT
COALESCE(LOCATION,'TOTAL'),
SUM(CASE WHEN my_date >= '10/1/2009' AND my_date <'01/01/2010' THEN QTY
ELSE NULL END) AS QTR1,
SUM(CASE WHEN my_date >= '01/1/2010' AND my_date <'04/01/2010' THEN QTY
ELSE NULL END) AS QTR2,
SUM(CASE WHEN my_date >= '04/1/2010' AND my_date <'07/01/2010' THEN QTY
ELSE NULL END) AS QTR3,
SUM(CASE WHEN my_date >= '07/1/2010' AND my_date <'10/01/2010' THEN QTY
ELSE NULL END) AS QTR4
FROM MYTABLE
GROUP BY LOCATION WITH ROLLUP
Returns
QTR1 QTR2 QTR3 QTR4
----- ----------- ----------- ----------- -----------
FL 1 NULL NULL 103
MD 27 1024 NULL NULL
TOTAL 28 1024 NULL 103