Grand Totals in Group By

If we need to show multiple levels (granularities) in different row then click here, If we need to have totals of higher levels (granularities) in the same row, let’s say our query is at day level we want to show the yearly totals in each row, one way to achieve it create the year level query, day level query and join them to get the desired output but the same can be obtained by using windowed function as shown below

 

SQL Server and Oracle:

 

Script:

 

As usual I will be using CTE to create the needed temporary table and records

 

; WITH TABLE1 AS  -- remove semicolon for oracle

(

SELECT

'20140101' AS COL_DATE,'2014' AS COL_YEAR,'01' AS COL_MONTH, 10 AS ORDER_QUANTITY

-- for oracle add "from dual"

UNION ALL

SELECT

'20140101' AS COL_DATE,'2014' AS COL_YEAR,'01' AS COL_MONTH, 11 AS ORDER_QUANTITY

-- for oracle add "from dual"

UNION ALL

SELECT

'20140102' AS COL_DATE,'2014' AS COL_YEAR,'01' AS COL_MONTH, 12 AS ORDER_QUANTITY

 -- for oracle add "from dual"

UNION ALL

SELECT

'20140102' AS COL_DATE,'2014' AS COL_YEAR,'01' AS COL_MONTH, 13 AS ORDER_QUANTITY

 -- for oracle add "from dual"

UNION ALL

SELECT

'20130101' AS COL_DATE,'2013' AS COL_YEAR,'01' AS COL_MONTH, 14 AS ORDER_QUANTITY

-- for oracle add "from dual"

)

 

SELECT COL_DATE

,COL_MONTH

,COL_YEAR

,SUM(ORDER_QUANTITY) AS ORDER_QUANTITY

,SUM(SUM(ORDER_QUANTITY)) OVER (PARTITION BY COL_YEAR) AS YEAR_TOTAL

,SUM(SUM(ORDER_QUANTITY)) OVER () AS GRAND_TOTAL

FROM TABLE1

GROUP BY COL_DATE,COL_MONTH,COL_YEAR

 

Output:

 

COL_DATE

COL_MONTH

COL_YEAR

ORDER_QUANTITY

YEAR_TOTAL

GRAND_TOTAL

20130101

01

2013

14

14

60

20140101

01

2014

21

46

60

20140102

01

2014

25

46

60

 

As shown above to get the grand total we should have nothing in the over condition of the windowed function, i.e. “SUM (SUM (column)) OVER ()”, if would like to have yearly total we need add the year as part of partition, i.e. “SUM (SUM (column)) OVER (PARTITION BY COL_YEAR)”, similarly for monthly total we need to have both year and month as part of partition by clause.

 

Note: The above example only demonstrates sum of sum but we can do any combination of aggregations.

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.