oracle行总计,汇总SQL Server或Oracle中的聚合

做

GROUP BY LOCATION WITH 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

ELSE NULL END) AS QTR1,

SUM(CASE WHEN my_date >= '01/1/2010' AND my_date

ELSE NULL END) AS QTR2,

SUM(CASE WHEN my_date >= '04/1/2010' AND my_date

ELSE NULL END) AS QTR3,

SUM(CASE WHEN my_date >= '07/1/2010' AND my_date

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