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.

GROUPING SETS,ROLLUP and CUBE

If we need to group the same data at multiple granularities (levels), Let’s say at day level, month level and year level all we will be doing is create the same script multiple times with day columns removed at month level script, day and month columns removed at year level script and execute them separately leading to multiple scripts hitting the same tables in our database, alternatively we can use Grouping Sets, Rollup and Cubes to achieve the same with a single script and reducing the number of hits to the server.

 

Grouping Sets

 

SQL Server and Oracle:

 

Syntax:

            Select <<Grouping columns>>

, <<Aggregated Columns>>

,Grouping_ID (<<Grouping columns>>)

, GROUPING(<<Grouping columns>>)

            From <<Tables>>

            Group by Grouping Sets (

                                                            (<<Grouping columns set1>>)

                                                            , (<<Grouping columns set2>>)

                                                            …        

                                                     )

 

 

Example:

For simplicity of understanding I am using date column, year column, month column and order quantity is the aggregation column (SUM)

 

As shown below for day level we are including all the columns, month level we are excluding day column and year level we are

 

GROUPING

 

GROUPING Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 if not part of grouping or aggregated and 0 if it is part of grouping or not aggregated in the result set.

So for below example for year level i.e. col_date and col_month are not part of grouping and col_year is part of grouping and the values are GROUPING(col_date) =1 , GROUPING(col_month) =1 and GROUPING(col_year) =0

 

 

 GROUPING_ID

            Grouping_ID indicates the integer equivalent of binary value of the list of columns, so the binary value of Year Level i.e is 110 as GROUPING(col_date) =1 and GROUPING(col_month) =1 and GROUPING(col_year) =0 and the integer equivalent of 110 is 6(4+2+0)

 

If the order of columns in grouping_id list is changed the output will also change for example for the same year level if we have gropuing columns in opposite order i.e. GROUPING_ID(col_year,col_month, col_date)  will be 011 so the integer equivalent is 3 (0+2+1)

 

Script:

;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

'20140102' AS COL_DATE

,'2014' AS COL_YEAR

,'01' AS COL_MONTH

, 11 AS ORDER_QUANTITY

-- for oracle add "from dual"

UNION ALL

SELECT

'20140105' AS COL_DATE

,'2014' AS COL_YEAR

,'01' AS COL_MONTH

, 12 AS ORDER_QUANTITY

 -- for oracle add "from dual"

UNION ALL

SELECT

'20140201' AS COL_DATE

,'2014' AS COL_YEAR

,'02' AS COL_MONTH

, 13 AS ORDER_QUANTITY

 -- for oracle add "from dual"

UNION ALL

SELECT

'20140204' AS COL_DATE

,'2014' AS COL_YEAR

,'02' AS COL_MONTH

, 14 AS ORDER_QUANTITY

-- for oracle add "from dual"

)

 

SELECT

SUM(ORDER_QUANTITY) AS ORDER_QUANTITY

,COL_DATE

,COL_MONTH

,COL_YEAR

,CASE

WHEN GROUPING(COL_DATE) =1

AND GROUPING(COL_MONTH) =1

AND GROUPING(COL_YEAR) =0

        THEN 'YEAR_LEVEL'

WHEN GROUPING(COL_DATE) =1

AND GROUPING(COL_MONTH) =0

AND GROUPING(COL_YEAR) =0

        THEN 'MONTH_LEVEL'

WHEN  GROUPING(COL_DATE) =0

AND GROUPING(COL_MONTH) =0

AND GROUPING(COL_YEAR) =0

         THEN  'DAY_LEVEL'

 END AS GROUPING_DATA_LEVEL

,CASE

WHEN GROUPING_ID(COL_DATE,COL_MONTH,COL_YEAR)=6

       THEN 'YEAR_LEVEL'

            WHEN GROUPING_ID(COL_DATE,COL_MONTH,COL_YEAR)=4

       THEN 'MONTH_LEVEL'

             WHEN GROUPING_ID(COL_DATE,COL_MONTH,COL_YEAR)=0

        THEN  'DAY_LEVEL'

 END AS GROUPING_ID_DATA_LEVEL

FROM TABLE1

GROUP BY GROUPING SETS (

(

COL_DATE

,COL_MONTH

,COL_YEAR

),

(

COL_MONTH

,COL_YEAR

),

(

COL_YEAR

)

       )

 

ROLLUP:

 

If all we are trying to do is getting all levels of data i.e day level, month level and year level then we can use rollup, all rollup does is if we specify ROLLUP (a,b,c) it will output records with group by (a, b, c),group by (a, b), group by (a) and a grand total rows. In our case col_year is a, col_month is b and col_date is c.

 

So we will surely have attributes or columns specific to each level what should we do? simple we have to put the same level columns inside brackets i.e. let’s say we have Day_of_Year column which has date and is day level then our group by will be

GROUP BY ROLLUP (COL_YEAR,COL_MONTH,( COL_DATE, Day_of_Year) )

 

Script:

;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

'20140102' AS COL_DATE

,'2014' AS COL_YEAR

,'01' AS COL_MONTH

, 11 AS ORDER_QUANTITY

-- for oracle add "from dual"

UNION ALL

SELECT

'20140105' AS COL_DATE

,'2014' AS COL_YEAR

,'01' AS COL_MONTH

, 12 AS ORDER_QUANTITY

 -- for oracle add "from dual"

UNION ALL

SELECT

'20140201' AS COL_DATE

,'2014' AS COL_YEAR

,'02' AS COL_MONTH

, 13 AS ORDER_QUANTITY

 -- for oracle add "from dual"

UNION ALL

SELECT

'20140204' AS COL_DATE

,'2014' AS COL_YEAR

,'02' AS COL_MONTH

, 14 AS ORDER_QUANTITY

-- for oracle add "from dual"

)

 

SELECT

SUM(ORDER_QUANTITY)

,COL_DATE

,COL_MONTH

,COL_YEAR

,CASE

WHEN GROUPING(COL_DATE) =1

AND GROUPING(COL_MONTH) =1

AND GROUPING(COL_YEAR) =0

        THEN 'YEAR_LEVEL'

WHEN GROUPING(COL_DATE) =1

AND GROUPING(COL_MONTH) =0

AND GROUPING(COL_YEAR) =0

        THEN 'MONTH_LEVEL'

WHEN  GROUPING(COL_DATE) =0

AND GROUPING(COL_MONTH) =0

AND GROUPING(COL_YEAR) =0

         THEN  'DAY_LEVEL'

 END AS GROUPING_DATA_LEVEL

,CASE

WHEN GROUPING_ID(COL_DATE,COL_MONTH,COL_YEAR)=6

       THEN 'YEAR_LEVEL'

            WHEN GROUPING_ID(COL_DATE,COL_MONTH,COL_YEAR)=4

       THEN 'MONTH_LEVEL'

             WHEN GROUPING_ID(COL_DATE,COL_MONTH,COL_YEAR)=0

        THEN  'DAY_LEVEL'

 END AS GROUPING_ID_DATA_LEVEL

FROM TABLE1

GROUP BY ROLLUP (

COL_YEAR

,COL_MONTH

, COL_DATE    

                                           )

 

CUBE:

            Cube grouping outputs all the combination of groupings for the group by columns, for example if we have CUBE (a,b,c) it will output records with group by (a, b, c), (a, b), (a, c), (b, c), (a), (b),(c) and a grand total rows.

 

Script:

;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

'20140102' AS COL_DATE

,'2014' AS COL_YEAR

,'01' AS COL_MONTH

, 11 AS ORDER_QUANTITY

-- for oracle add "from dual"

UNION ALL

SELECT

'20140105' AS COL_DATE

,'2014' AS COL_YEAR

,'01' AS COL_MONTH

, 12 AS ORDER_QUANTITY

 -- for oracle add "from dual"

UNION ALL

SELECT

'20140201' AS COL_DATE

,'2014' AS COL_YEAR

,'02' AS COL_MONTH

, 13 AS ORDER_QUANTITY

 -- for oracle add "from dual"

UNION ALL

SELECT

'20140204' AS COL_DATE

,'2014' AS COL_YEAR

,'02' AS COL_MONTH

, 14 AS ORDER_QUANTITY

-- for oracle add "from dual"

)

 

SELECT

SUM(ORDER_QUANTITY)

,COL_DATE

,COL_MONTH

,COL_YEAR

, GROUPING_ID(COL_DATE,COL_MONTH,COL_YEAR)

FROM TABLE1

GROUP BY CUBE (

COL_YEAR

,COL_MONTH

, COL_DATE    

                                           )

ORDER BY GROUPING_ID(COL_DATE,COL_MONTH,COL_YEAR)

 

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

Difference between dates or DATEDIFF

To get the difference between two date columns we can use DATEDIFF function in SQL Server and in Oracle we can just subtract two columns as shown below

 

SQL Server DATEDIFF ( DATEPART , STARTDATE , ENDDATE )

Example:

Select Datediff(mm,column1,column2)

 

Above script will output difference in months, so based on what we specify as a first parameter (datepart) to function we will get different results, below are the list of parameters which can be used to get desired format.

 

“datepart” parameters:

year	yy, yyyy
quarter	qq, q
month	mm, m
Day of year	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
MICROSECOND	mcs
nanosecond	ns

 

Oracle

In Oracle as shown below we need to write a little complex script to achieve the desired results as shown below

 

Select trunc(column1-column2) as Day,

           trunc(mod((column1-column2)*24,24)) as Hour,

           trunc(mod((column1-column2)*24*60,60)) as Minutes,

           trunc(mod((column1-column2)*24*60*60)) as Seconds

From Dual;

 

If we would like to find difference in months we can use MONTHS_BETWEEN(column1, column2) function as shown below

 

SELECT

MONTHS_BETWEEN(SYSDATE, SYSDATE+30)

from   DUAL;

 

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

 

 

Add a date interval to date or DATEADD

Add a date interval to a date in SQL Server can be obtained by using DATEADD Function whereas in Oracle we can just add the value as shown below

 

SQL Server

 

DATEADD (datepart , number , date )

 

Example:

Select DATEADD (m,2,getdate())

 

as todays date is 06/20/2012 the output of the query is "08/20/2012", so based on what we specify as a first parameter (datepart) to function we will get different results, below are the list of parameters which can be used to get desired format.

 

“datepart” parameters:

year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
micro second	mcs
nanosecond	ns

 

Oracle

When it comes to oracle we can just add the numbers and 1 represents one day as shown below

SELECT SYSDATE AS current_date,

       SYSDATE + 1 AS plus_1_day,

       SYSDATE + 1/24 AS plus_1_hours,

       SYSDATE + 1/24/60 AS plus_1_minutes,

       SYSDATE + 1/24/60/60 AS plus_1_seconds

FROM   dual;

 

If we would like to add a month we can use ADD_MONTHS (column1, value) function as shown below

 

SELECT SYSDATE,

       ADD_MONTHS(SYSDATE, 2)

from   DUAL;

 

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

 

 

Generate Series or List of Numbers

To generate a list of numbers or series in Oracle or SQL Server, we need to use recursive CTE, if we call the CTE within the CTE then it is called recursive CTE

 

SQL Server:

 

; WITH REC_CTE (ROW_NUM) AS (

--create first record, if we want to start the series from 10 then change it from 1 to 10

SELECT 1 AS ROW_NUM

UNION ALL

--calling REC_CTE within REC_CTE and add value 1 to get next number

SELECT ROW_NUM+1 FROM REC_CTE

--limit the number of rows

WHERE ROW_NUM<100

)

--now select the CTE to get the list of values

SELECT * FROM REC_CTE;

 

Oracle 11g version 2 and higher:

 

WITH REC_CTE (ROW_NUM) AS (

--create first record, if we want to start the series from 10 then change it from 1 to 10

SELECT 1 AS ROW_NUM FROM DUAL

UNION ALL

--calling REC_CTE within REC_CTE and add value 1 to get next number

SELECT ROW_NUM+1 FROM REC_CTE

--limit the number of rows to 100

WHERE ROW_NUM<100

)

--now select the CTE to get the list of values

SELECT * FROM REC_CTE;

 

Oracle:

Oracle provides a set of hierarchy functions such as START WITH, LEVEL, PRIOR, CONNECT BY, CONNECT_BY_ROOT, see this link for more info

 

Example1:

            --below script will retrieve first hundred numbers

SELECT ROWNUM FROM DUAL

--limit the number of rows to 100

CONNECT BY ROWNUM <= 100;

 

Example2:

            --we can use level if we want to create a series from number other than 1

            SELECT LEVEL LVL FROM DUAL

            -- if we want to start the series from 10 then change it from 3 to 10

WHERE LEVEL >=3

--limit the number of rows to 100

CONNECT BY LEVEL<100

 

If you need to check the version of oracle, use below script

 

            SELECT * FROM V$VERSION

WHERE BANNER LIKE 'ORACLE%';

 

Above we have added 1 when we called the CTE with in CTE, if we add 2 then we will get even number series and so on.

 

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