Oracle -- Extended Group By Clause
ROLLUP(), CUBE(), GROUPING_SETS(), GROUPING(), GRUOPING_ID(), GROUP_ID()
1. ROLLUP()
--返回n+1层小计
a)
select division_id, job_id, sum(salary)
from EMPLOYEES2
group by division_id, job_id
order by division_id, job_id
b)
select division_id, job_id, sum(salary)
from EMPLOYEES2
group by rollup(division_id, job_id)
order by division_id, job_id
--use CASE + GROUPING() to convert multiple column values.
c)
select division_id, job_id, sum(salary)
from EMPLOYEES2
group by division_id, rollup(division_id, job_id)
order by division_id, job_id
--Compared with option b. Duplicate records returned by group by division_id, The grand total of all groups is not returned.
--use GROUP_ID() to filter out duplicate records.
d)
select division_id, job_id, sum(salary)
from EMPLOYEES2
group by division_id, rollup(job_id)
order by division_id, job_id
--Compared with option b. No duplicated records returned.
--Except the grand total of all groups is not returned, the rest result are as same as option b.
2, CUBE()
--返回2的N次方小计
3, GROUPIN()
--Return 1 if the column value is NULL, otherwise returns 0.
4, GROUP_ID()
--Return 0 to n-1 if n duplicate records exist.