WITH CUBE, WITH ROLLUP, and GROUPING tests in GROUP BY

Keywords: SQL Sever Java

A few days ago, I saw a group of friends using WITH ROLLUP. Because they didn't use it, I had no time to test it.

1. Concept:

WITH CUBE: the resulting set shows the aggregation of all combinations of values in the selected column.

WITH ROLLUP: the resulting set shows an aggregation of a hierarchy of values in the selected column.

Group: when a row is added by the WITH CUBE or WITH ROLLUP operator, this function will cause the output value of the additional column to be 1; when a row is not added by the CUBE or ROLLUP operator, this function will cause the output value of the additional column to be 0.

Grouping is only allowed in a selection list associated with a GROUP BY clause that contains a CUBE or ROLLUP operator.

II. Test:

1. Establish temporary table

CREATE TABLE #T0
(
    [GRADE] [VARCHAR](50) NULL,     --grade
    [CLASS] [VARCHAR](50) NULL,     --class
    [NAME] [VARCHAR](50) NULL,      --Full name
    [COURSE] [VARCHAR](50) NULL,    --subject
    [RESULT] [NUMERIC](8,2) NULL    --achievement
)

CREATE TABLE #T1
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,    --Serial number
    [GRADE] [VARCHAR](50) NULL,           --grade
    [CLASS] [VARCHAR](50) NULL,           --class
    [NAME] [VARCHAR](50) NULL,            --Full name
    [COURSE] [VARCHAR](50) NULL,          --subject
    [RESULT] [NUMERIC](8,2) NULL          --achievement
)

CREATE TABLE #T2
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,    --Serial number
    [GRADE] [VARCHAR](50) NULL,           --grade
    [CLASS] [VARCHAR](50) NULL,           --class
    [NAME] [VARCHAR](50) NULL,            --Full name
    [COURSE] [VARCHAR](50) NULL,          --subject
    [RESULT] [NUMERIC](8,2) NULL          --achievement
)

2. Insert test data

INSERT INTO #T0 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT '2019','CLASS1','9A01','C#',100
UNION
SELECT '2019','CLASS1','9A02','C#',100
UNION
SELECT '2019','CLASS2','9B01','C#',100
UNION
SELECT '2019','CLASS2','9B02','C#',100
UNION
SELECT '2018','CLASS1','8A01','JAVA',100
UNION
SELECT '2018','CLASS1','8A02','JAVA',100
UNION
SELECT '2018','CLASS2','8B01','JAVA',100
UNION
SELECT '2018','CLASS2','8B02','JAVA',100

Query T0 table results:

    3,GROUP BY

Sort: sort by default in the order of SELECT fields. The following two types of query results are the same.

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE
ORDER BY GRADE,CLASS,NAME,COURSE

    4,WITH CUBE

Principle 1: group aggregation is performed by assigning NULL values to GROUP BY fields in turn.

Principle 2: the first field is that all fields will be grouped and aggregated with NULL values, and the results will be sorted from right to left.

Start to test the result of the first field:

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUP BY GRADE,CLASS,NAME,COURSE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT 'ZZ' GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUP BY CLASS,NAME,COURSE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT 'ZZ' GRADE,'ZZ' CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUP BY NAME,COURSE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT 'ZZ' GRADE,'ZZ' CLASS,'ZZ' NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUP BY COURSE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT 'ZZ' GRADE,'ZZ' CLASS,'ZZ' NAME,'ZZ' COURSE,SUM(RESULT) RESULT 
FROM #T0

--The first field results are sorted from right to left
INSERT INTO #T2 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,RESULT FROM #T1 WHERE ID BETWEEN 1 AND 27 ORDER BY COURSE,NAME,CLASS,GRADE

UPDATE #T2 SET GRADE=NULL WHERE GRADE='ZZ'
UPDATE #T2 SET CLASS=NULL WHERE CLASS='ZZ'
UPDATE #T2 SET NAME=NULL WHERE NAME='ZZ'
UPDATE #T2 SET COURSE=NULL WHERE COURSE='ZZ'

Results of WITH CUBE:

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE
WITH CUBE

Self test results:

SELECT * FROM #T2

The result is the same as above.

Which field is the priority of other fields? And how to sort them? Well, I did. I didn't find out

    5,WITH ROLLUP

Principle 1: the last field of GROUP BY is assigned with NULL value for group aggregation.

Principle 2: the results are sorted from left to right.

This is very similar to the first field of WITH CUBE: one is the first field, the other is the last field; one result is sorted from right to left, and one result is sorted from left to right.

How do the test results come from

TRUNCATE TABLE #T1
TRUNCATE TABLE #T2

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUP BY GRADE,CLASS,NAME,COURSE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,'ZZ' COURSE,SUM(RESULT) RESULT 
FROM #T0 
WHERE NOT EXISTS (SELECT 1 FROM #T1 WHERE GRADE=#T0.GRADE AND CLASS=#T0.GRADE AND NAME=#T0.NAME AND COURSE='ZZ')
GROUP BY GRADE,CLASS,NAME

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,'ZZ' NAME,'ZZ' COURSE,SUM(RESULT) RESULT 
FROM #T0 
WHERE NOT EXISTS (SELECT 1 FROM #T1 WHERE GRADE=#T0.GRADE AND CLASS=#T0.CLASS AND NAME='ZZ' AND COURSE='ZZ')
GROUP BY GRADE,CLASS

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,'ZZ' CLASS,'ZZ' NAME,'ZZ' COURSE,SUM(RESULT) RESULT 
FROM #T0 
WHERE NOT EXISTS (SELECT 1 FROM #T1 WHERE GRADE=#T0.GRADE AND CLASS='ZZ' AND NAME='ZZ' AND COURSE='ZZ')
GROUP BY GRADE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT 'ZZ' GRADE,'ZZ' CLASS,'ZZ' NAME,'ZZ' COURSE,SUM(RESULT) RESULT 
FROM #T0 

--Sort results left to right
INSERT INTO #T2 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,RESULT FROM #T1 ORDER BY GRADE,CLASS,NAME,COURSE

UPDATE #T2 SET GRADE=NULL WHERE GRADE='ZZ'
UPDATE #T2 SET CLASS=NULL WHERE CLASS='ZZ'
UPDATE #T2 SET NAME=NULL WHERE NAME='ZZ'
UPDATE #T2 SET COURSE=NULL WHERE COURSE='ZZ'

Results of WITH ROLLUP:

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE
WITH ROLLUP

Self test results:

SELECT * FROM #T2

The result is the same as above.

    6,GROUPING

This is easy to understand. The usage of WITH CUBE is the same as that of WITH ROLLUP. First look at the result:

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT,GROUPING(COURSE) [GROUPING]
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE
WITH ROLLUP

The above group is the COURSE field. If there is a NULL value, it is added additionally WITH ROLLUP. The group result value is 1.

With GROUPING, it is convenient to make subtotal and total.

SELECT 
    GRADE,
    CASE WHEN GROUPING(GRADE)=1 AND GROUPING(CLASS)=1 THEN 'Total' WHEN GROUPING(GRADE)=0 AND GROUPING(CLASS)=1 THEN 'Subtotal' ELSE CLASS END CLASS,
    NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE
WITH ROLLUP

OK, that's the end of the test.

Posted by evilmonkey on Mon, 04 Nov 2019 12:02:42 -0800