Group by cube, rollup, grouping set usage

We usually use the most select sum (Quantity) from table name group by department, name with ur;
In fact, DB2 has several other usage cases about group by. X will now post the study notes:
Special notes on group:
Select department, name, sum (amount) as sum, count (*) as Count
From sale
Inner join employee on liaohaibing. employee. ID = liaohaibing. Sale. employeeid
Inner join liaohaibing. Department on
Liaohaibing. Department. Dimension mentid = liaohaibing. Employee mentid
Where liaohaibing. Department. Dimension mentid in)
Group by rollup (department, name );
The results are shown in the following table.
Department name sum count
184780 128
Logistics Department 58554 48
Development Department 48625 32
Asset Management Department 77601 48
Logistics Department fff 15033 16
Logistics Department ggg 18000 16
Logistics Department qqq 25521 16
Development Department LHB 26293 16
Development Team liaohaibing 22332 16
Asset Management Department CCC 28936 16
Asset Management Department DDD 28033 16
Asset Management Department EEE 20632 16

Rollup

Group by rollup (department, name)
The first is to display all and to the first line.
The second is the display by group by department, which is grouped by department.
The third is the display by group by department, name, which is grouped by the name of a department.
There is also a parameter: Cube,
Example: Select sales_date, sales_person,
Sum (sales) as units_sold
--, Grouping (sales_date) as date_group,
-- Grouping (sales_person) as sales_group
From sales
Group by cube (sales_date, sales_person)
-- Order by sales_date, sales_person;
Cube:
Group by cube (sales_date, sales_person)
The first is to display all and to the first line.
The second is grouping by group by sales_date.
The third is grouping by group by sales_person.
The fourth is to group by sales_date and sales_person.
Another parameter is group by sets.
Select sales_date, sales_person,
Sum (sales) as units_sold
--, Grouping (sales_date) as date_group,
-- Grouping (sales_person) as sales_group
From sales
Group by grouping sets (sales_date, sales_person ,())
-- Order by sales_date, sales_person;
Grouping Sets
Group by grouping sets (sales_date, sales_person ,())

The first is to display all and to the first line.
Group by sales_date.
The third is grouping by group by sales_person.

Http://blog.renren.com/share/119551041/3061109556