GROUP BY queries and the equivalent GROUPING SET queries

Records in the table:

SQL> select * from Grouping_tbl;

A B AMT
-- --- ----------
10 101 55
10 102 75
20 8001 35
20 8002 45
10 101 25
20 8001 25

6 rows selected.

Script 1:

SQL> SELECT a, b, SUM(amt) FROM Grouping_tbl GROUP BY a, b;

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
20 8001 60
20 8002 45

SQL> SELECT a, b, SUM(amt) FROM Grouping_tbl GROUP BY GROUPING SETS ( (a,b) );

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
20 8001 60
20 8002 45

Script 2:


SQL> SELECT a, b, SUM( amt ) FROM Grouping_tbl GROUP BY a, b
2 UNION
3 SELECT a, null, SUM( amt ) FROM Grouping_tbl GROUP BY a
4 ;

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
10 155
20 8001 60
20 8002 45
20 105

6 rows selected.

SQL> SELECT a, b, SUM( amt ) FROM Grouping_tbl GROUP BY GROUPING SETS ( (a,b), a) ;

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
10 155
20 8001 60
20 8002 45
20 105

6 rows selected.


Script 3:

SQL> SELECT a, null, SUM( amt ) FROM Grouping_tbl GROUP BY a
2 UNION
3 SELECT null, b, SUM( amt ) FROM Grouping_tbl GROUP BY b;

A B SUM(AMT)
--- ----- ------
10 155
20 105
101 80
102 75
8001 60
8002 45

6 rows selected.

SQL> SELECT a,b, SUM( amt ) FROM Grouping_tbl GROUP BY GROUPING SETS (a,b) ;

A B SUM(AMT)
--- ----- ------
10 155
20 105
101 80
102 75
8001 60
8002 45

6 rows selected.

Script 4:


SQL> SELECT a, b, SUM( amt ) FROM Grouping_tbl GROUP BY a, b UNION
2 SELECT a, null, SUM( amt ) FROM Grouping_tbl GROUP BY a, null UNION
3 SELECT null, b, SUM( amt ) FROM Grouping_tbl GROUP BY null, b UNION
4 SELECT null, null, SUM( amt ) FROM Grouping_tbl;

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
10 155
20 8001 60
20 8002 45
20 105
101 80
102 75
8001 60
8002 45
260

11 rows selected.

SQL> SELECT a, b, SUM( amt ) FROM Grouping_tbl GROUP BY GROUPING SETS ( (a, b), a, b, ( ) );

A B SUM(AMT)
--- ----- ------
10 101 80
10 102 75
10 155
20 8001 60
20 8002 45
20 105
101 80
102 75
8001 60
8002 45
260

11 rows selected.