postgresql has provided rollup/cube/grouping sets grouping functions since 9.5, which is more convenient to use. Especially when sql is used directly to produce reports, one sql can get all the details and summary values.
https://www.postgresql.org/docs/9.5/static/sql-select.html
https://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS
and grouping_element can be one of:
( )
expression
( expression [, ...] )
ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
CUBE ( { expression | ( expression [, ...] ) } [, ...] )
GROUPING SETS ( grouping_element [, ...] )test data
with tmp_tab as (
select '20180205' as day_id,'1001' as custno,'a001' as data_type,1 as qty union all
select '20180205' as day_id,'1001' as custno,'a002' as data_type,2 as qty union all
select '20180205' as day_id,'1002' as custno,'a001' as data_type,3 as qty union all
select '20180205' as day_id,'1002' as custno,'a003' as data_type,4 as qty union all
select '20180206' as day_id,'1001' as custno,'a004' as data_type,5 as qty union all
select '20180207' as day_id,'1003' as custno,'a001' as data_type,6 as qty
)
select grouping(t0.day_id) as day_id,
grouping(t0.data_type) as data_type,
grouping(t0.custno) as custno,
t0.day_id,
t0.data_type,
t0.custno,
sum(t0.qty)
from tmp_tab t0
group by t0.day_id,t0.data_type,t0.custnogrouping sets: Displays the specified aggregate value
1,group by grouping sets ( (t0.day_id,t0.data_type,t0.custno) )
Equivalent to
group by t0.day_id,t0.data_type,t0.custno
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 0 | 0 | 20180205 | a001 | 1001 | 1
0 | 0 | 0 | 20180207 | a001 | 1003 | 6
0 | 0 | 0 | 20180205 | a002 | 1001 | 2
0 | 0 | 0 | 20180205 | a001 | 1002 | 3
0 | 0 | 0 | 20180205 | a003 | 1002 | 4
0 | 0 | 0 | 20180206 | a004 | 1001 | 5
(6 rows)2,group by grouping sets ( t0.day_id,t0.data_type,t0.custno )
For t0.day_id,t0.data_type,t0.custno, the three columns are summed up separately.
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 1 | 1 | 20180205 | | | 10
0 | 1 | 1 | 20180206 | | | 5
0 | 1 | 1 | 20180207 | | | 6
1 | 1 | 0 | | | 1001 | 8
1 | 1 | 0 | | | 1002 | 7
1 | 1 | 0 | | | 1003 | 6
1 | 0 | 1 | | a001 | | 10
1 | 0 | 1 | | a002 | | 2
1 | 0 | 1 | | a003 | | 4
1 | 0 | 1 | | a004 | | 5
(10 rows)3,group by grouping sets ( (t0.day_id,t0.data_type),t0.custno )
(t0.day_id,t0.data_type) as a whole
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
1 | 1 | 0 | | | 1001 | 8
1 | 1 | 0 | | | 1002 | 7
1 | 1 | 0 | | | 1003 | 6
0 | 0 | 1 | 20180205 | a001 | | 4
0 | 0 | 1 | 20180205 | a002 | | 2
0 | 0 | 1 | 20180205 | a003 | | 4
0 | 0 | 1 | 20180206 | a004 | | 5
0 | 0 | 1 | 20180207 | a001 | | 6
(8 rows)
4,group by grouping sets ( (t0.day_id),(t0.data_type),(t0.custno) )
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 1 | 1 | 20180205 | | | 10
0 | 1 | 1 | 20180206 | | | 5
0 | 1 | 1 | 20180207 | | | 6
1 | 1 | 0 | | | 1001 | 8
1 | 1 | 0 | | | 1002 | 7
1 | 1 | 0 | | | 1003 | 6
1 | 0 | 1 | | a001 | | 10
1 | 0 | 1 | | a002 | | 2
1 | 0 | 1 | | a003 | | 4
1 | 0 | 1 | | a004 | | 5
(10 rows)
rollup: Increase the summary line while retaining group by
1,group by rollup ( (t0.day_id,t0.data_type,t0.custno) )
For t0.day_id,t0.data_type,t0.custno, we sum up the three columns as a whole.
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 0 | 0 | 20180205 | a001 | 1001 | 1
0 | 0 | 0 | 20180205 | a001 | 1002 | 3
0 | 0 | 0 | 20180205 | a002 | 1001 | 2
0 | 0 | 0 | 20180205 | a003 | 1002 | 4
0 | 0 | 0 | 20180206 | a004 | 1001 | 5
0 | 0 | 0 | 20180207 | a001 | 1003 | 6
1 | 1 | 1 | | | | 21
(7 rows)
2,group by rollup ( t0.day_id,t0.data_type,t0.custno )
For t0.day_id,t0.data_type,t0.custno, sum up in turn
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 0 | 0 | 20180205 | a001 | 1001 | 1
0 | 0 | 0 | 20180205 | a001 | 1002 | 3
0 | 0 | 1 | 20180205 | a001 | | 4
0 | 0 | 0 | 20180205 | a002 | 1001 | 2
0 | 0 | 1 | 20180205 | a002 | | 2
0 | 0 | 0 | 20180205 | a003 | 1002 | 4
0 | 0 | 1 | 20180205 | a003 | | 4
0 | 1 | 1 | 20180205 | | | 10
0 | 0 | 0 | 20180206 | a004 | 1001 | 5
0 | 0 | 1 | 20180206 | a004 | | 5
0 | 1 | 1 | 20180206 | | | 5
0 | 0 | 0 | 20180207 | a001 | 1003 | 6
0 | 0 | 1 | 20180207 | a001 | | 6
0 | 1 | 1 | 20180207 | | | 6
1 | 1 | 1 | | | | 21
(15 rows)
3,group by rollup ( (t0.day_id,t0.data_type),t0.custno )
(t0.day_id,t0.data_type) as a whole
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 0 | 0 | 20180205 | a001 | 1001 | 1
0 | 0 | 0 | 20180205 | a001 | 1002 | 3
0 | 0 | 1 | 20180205 | a001 | | 4
0 | 0 | 0 | 20180205 | a002 | 1001 | 2
0 | 0 | 1 | 20180205 | a002 | | 2
0 | 0 | 0 | 20180205 | a003 | 1002 | 4
0 | 0 | 1 | 20180205 | a003 | | 4
0 | 0 | 0 | 20180206 | a004 | 1001 | 5
0 | 0 | 1 | 20180206 | a004 | | 5
0 | 0 | 0 | 20180207 | a001 | 1003 | 6
0 | 0 | 1 | 20180207 | a001 | | 6
1 | 1 | 1 | | | | 21
(12 rows)
4,group by rollup ( (t0.day_id),(t0.data_type),(t0.custno) )
Equivalent to the second case
group by rollup ( t0.day_id,t0.data_type,t0.custno )
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 0 | 0 | 20180205 | a001 | 1001 | 1
0 | 0 | 0 | 20180205 | a001 | 1002 | 3
0 | 0 | 1 | 20180205 | a001 | | 4
0 | 0 | 0 | 20180205 | a002 | 1001 | 2
0 | 0 | 1 | 20180205 | a002 | | 2
0 | 0 | 0 | 20180205 | a003 | 1002 | 4
0 | 0 | 1 | 20180205 | a003 | | 4
0 | 1 | 1 | 20180205 | | | 10
0 | 0 | 0 | 20180206 | a004 | 1001 | 5
0 | 0 | 1 | 20180206 | a004 | | 5
0 | 1 | 1 | 20180206 | | | 5
0 | 0 | 0 | 20180207 | a001 | 1003 | 6
0 | 0 | 1 | 20180207 | a001 | | 6
0 | 1 | 1 | 20180207 | | | 6
1 | 1 | 1 | | | | 21
(15 rows)
cube: Increase the cross-summary row of the specified column on the basis of retaining group by
1,group by cube ( (t0.day_id,t0.data_type,t0.custno) )
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 0 | 0 | 20180205 | a001 | 1001 | 1
0 | 0 | 0 | 20180205 | a001 | 1002 | 3
0 | 0 | 0 | 20180205 | a002 | 1001 | 2
0 | 0 | 0 | 20180205 | a003 | 1002 | 4
0 | 0 | 0 | 20180206 | a004 | 1001 | 5
0 | 0 | 0 | 20180207 | a001 | 1003 | 6
1 | 1 | 1 | | | | 21
(7 rows)2,group by cube ( t0.day_id,t0.data_type,t0.custno )
t0.day_id,t0.data_type,t0.custno
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 0 | 0 | 20180205 | a001 | 1001 | 1
0 | 0 | 0 | 20180205 | a001 | 1002 | 3
0 | 0 | 1 | 20180205 | a001 | | 4
0 | 0 | 0 | 20180205 | a002 | 1001 | 2
0 | 0 | 1 | 20180205 | a002 | | 2
0 | 0 | 0 | 20180205 | a003 | 1002 | 4
0 | 0 | 1 | 20180205 | a003 | | 4
0 | 1 | 1 | 20180205 | | | 10
0 | 0 | 0 | 20180206 | a004 | 1001 | 5
0 | 0 | 1 | 20180206 | a004 | | 5
0 | 1 | 1 | 20180206 | | | 5
0 | 0 | 0 | 20180207 | a001 | 1003 | 6
0 | 0 | 1 | 20180207 | a001 | | 6
0 | 1 | 1 | 20180207 | | | 6
1 | 1 | 1 | | | | 21
0 | 1 | 0 | 20180205 | | 1001 | 3
0 | 1 | 0 | 20180206 | | 1001 | 5
1 | 1 | 0 | | | 1001 | 8
0 | 1 | 0 | 20180205 | | 1002 | 7
1 | 1 | 0 | | | 1002 | 7
0 | 1 | 0 | 20180207 | | 1003 | 6
1 | 1 | 0 | | | 1003 | 6
1 | 0 | 0 | | a001 | 1001 | 1
1 | 0 | 0 | | a001 | 1002 | 3
1 | 0 | 0 | | a001 | 1003 | 6
1 | 0 | 1 | | a001 | | 10
1 | 0 | 0 | | a002 | 1001 | 2
1 | 0 | 1 | | a002 | | 2
1 | 0 | 0 | | a003 | 1002 | 4
1 | 0 | 1 | | a003 | | 4
1 | 0 | 0 | | a004 | 1001 | 5
1 | 0 | 1 | | a004 | | 5
(32 rows)
3,group by cube ( (t0.day_id,t0.data_type),t0.custno )
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 0 | 0 | 20180205 | a001 | 1001 | 1
0 | 0 | 0 | 20180205 | a002 | 1001 | 2
0 | 0 | 0 | 20180206 | a004 | 1001 | 5
1 | 1 | 0 | | | 1001 | 8
0 | 0 | 0 | 20180205 | a001 | 1002 | 3
0 | 0 | 0 | 20180205 | a003 | 1002 | 4
1 | 1 | 0 | | | 1002 | 7
0 | 0 | 0 | 20180207 | a001 | 1003 | 6
1 | 1 | 0 | | | 1003 | 6
1 | 1 | 1 | | | | 21
0 | 0 | 1 | 20180205 | a001 | | 4
0 | 0 | 1 | 20180205 | a002 | | 2
0 | 0 | 1 | 20180205 | a003 | | 4
0 | 0 | 1 | 20180206 | a004 | | 5
0 | 0 | 1 | 20180207 | a001 | | 6
(15 rows)
4,group by cube ( (t0.day_id),(t0.data_type),(t0.custno) )
day_id | data_type | custno | day_id | data_type | custno | sum
--------+-----------+--------+----------+-----------+--------+-----
0 | 0 | 0 | 20180205 | a001 | 1001 | 1
0 | 0 | 0 | 20180205 | a001 | 1002 | 3
0 | 0 | 1 | 20180205 | a001 | | 4
0 | 0 | 0 | 20180205 | a002 | 1001 | 2
0 | 0 | 1 | 20180205 | a002 | | 2
0 | 0 | 0 | 20180205 | a003 | 1002 | 4
0 | 0 | 1 | 20180205 | a003 | | 4
0 | 1 | 1 | 20180205 | | | 10
0 | 0 | 0 | 20180206 | a004 | 1001 | 5
0 | 0 | 1 | 20180206 | a004 | | 5
0 | 1 | 1 | 20180206 | | | 5
0 | 0 | 0 | 20180207 | a001 | 1003 | 6
0 | 0 | 1 | 20180207 | a001 | | 6
0 | 1 | 1 | 20180207 | | | 6
1 | 1 | 1 | | | | 21
0 | 1 | 0 | 20180205 | | 1001 | 3
0 | 1 | 0 | 20180206 | | 1001 | 5
1 | 1 | 0 | | | 1001 | 8
0 | 1 | 0 | 20180205 | | 1002 | 7
1 | 1 | 0 | | | 1002 | 7
0 | 1 | 0 | 20180207 | | 1003 | 6
1 | 1 | 0 | | | 1003 | 6
1 | 0 | 0 | | a001 | 1001 | 1
1 | 0 | 0 | | a001 | 1002 | 3
1 | 0 | 0 | | a001 | 1003 | 6
1 | 0 | 1 | | a001 | | 10
1 | 0 | 0 | | a002 | 1001 | 2
1 | 0 | 1 | | a002 | | 2
1 | 0 | 0 | | a003 | 1002 | 4
1 | 0 | 1 | | a003 | | 4
1 | 0 | 0 | | a004 | 1001 | 5
1 | 0 | 1 | | a004 | | 5
(32 rows)
postgresql's grouping sets/rollup/cube is the same as oracle's, simple to use and powerful.