总结 SQL Server 或 Oracle 中的聚合

Question

我正在运行下面的查询，需要在底部包含总计，但不确定如何在 SQL Server 中运行它。我可以复制数据并在 Excel 中进行自动求和，但这有点重复。我怎样才能实现这个目标？

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

GROUP BY LOCATION AND ROLLUP 是否满足您的需要？

;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

退货

      QTR1        QTR2        QTR3        QTR4
----- ----------- ----------- ----------- -----------
FL    1           NULL        NULL        103
MD    27          1024        NULL        NULL
TOTAL 28          1024        NULL        103

Answer 2

我认为这个的变体应该有效。只需删除按位置分组并使用以下查询：

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