Oracle -- Extended Group By Clause

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.