Usage and difference of group, grouping, rollup, and cube

-- Create a test table
Use test
Create Table Test (ID int, sort char (10), color char (10), num int constraint pk_test primary key (ID, sort, color ))

-- Insert data
Insert into test
Select 1, 'book', 'Blue ', 10
Union all
Select 1, 'book', 'green', 10
Union all
Select 1, 'book', 'red', 10
Union all
Select 1, 'car', 'Blue ', 10
Union all
Select 1, 'car', 'red', 10
Union all
Select 2, 'car', 'red', 10


-- Group
Select sort, color, sum (Num) as num from test group by sort, color

-- Output result
-- Book blue 10
-- Car blue 10
-- Book Green 10
-- Book red 10
-- Car Red 20



-- Group by with Rollup
Select
Case
When grouping (SORT) = 1 then 'all'
Else isnull (sort, 'unknow ')
End as sort,
Case
When grouping (color) = 1 then 'all'
Else isnull (color, 'unknow ')
End as color,
Sum (Num) as num from test
Group by sort, color with Rollup

-- Output result
-- Book blue 10
-- Book Green 10
-- Book red 10
-- Book all 30
-- Car blue 10
-- Car Red 20
-- Car all 30
-- All all 60



-- Group by with cube
Select
Case
When grouping (SORT) = 1 then 'all'
Else isnull (sort, 'unknow ')
End as sort,
Case
When grouping (color) = 1 then 'all'
Else isnull (color, 'unknow ')
End as color,
Sum (Num) as num from test
Group by sort, color with cube

-- Output result
-- Book blue 10
-- Book Green 10
-- Book red 10
-- Book all 30
-- Car blue 10
-- Car Red 20
-- Car all 30
-- All all 60
-- All Blue 20
-- All green 10
-- All Red 30

Summary:
1. The difference between cube and rollup is:
• The result set generated by cube displays the aggregation of all the combinations of the values in the selected column.
• The result set generated by rollup displays the aggregation of a certain hierarchy of values in the selected column.
2. grouping is an aggregate function that generates an additional column. When a row is added using the cube or rollup operator, the output value of the appended column is 1, when the added row is not produced by cube or rollup, the value of the appended column is 0.
Grouping is allowed only in the selection list associated with the group by clause that contains the cube or rollup operator.