Sum up the aggregrate in SQL Server or ORACLE

Question

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 repeatative) Could some one please help?

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   ??? ??? ??? ???

Answer 1

I think a variant of this should work. Just remove the grouping by location and use this query:

SELECT 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

Answer 2

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