Working with Group By Extensions – ROLLUP,CUBE,GROUPING SETS

Code:
-----------
use ramu;

create or replace table sales (product_id integer, retail_price real,
quantity integer, city varchar, state varchar);


insert into sales (product_id, retail_price, quantity, city, state) values
(1, 2.00, 1, 'SF', 'CA'),
(1, 2.00, 2, 'SJ', 'CA'),
(2, 5.00, 4, 'SF', 'CA'),
(2, 5.00, 8, 'SJ', 'CA'),
(2, 5.00, 16, 'Miami', 'FL'),
(2, 5.00, 32, 'Orlando', 'FL'),
(2, 5.00, 64, 'SJ', 'CA');

select * from sales;

select CITY,sum(quantity) from sales group by (CITY);
select STATE,sum(quantity) from sales group by (STATE);

select CITY,STATE,sum(quantity) from sales group by (CITY,STATE);
(select CITY,NULL as State,sum(quantity) from sales group by (CITY)) union all
(select NULL as CITY,STATE,sum(quantity) from sales group by (STATE));

--grouping set
select CITY,STATE,sum(quantity) from sales group by grouping sets (CITY,STATE);

--rollup
select CITY,STATE,sum(quantity) from sales group by rollup (STATE,CITY);

(STATE,CITY)
--------------------
(STATE,CITY)
(STATE)
()

(A,B,C)
---------
(A,B,C)
(A,B)
(A)
()

;
--cube
select CITY,STATE,sum(quantity) from sales group by cube (STATE,CITY);
(STATE,CITY)
-------------------
(STATE,CITY)
(CITY)
(STATE)
()
(A,B,C)
-------
(A,B,C)
(A,B)
(B,C)
(A,C)
(A)
(B)
(C)
()

Check this playlist for more AWS Projects in Big Data domain:
https://youtube.com/playlist?list=PLjfRmoYoxpNopPjdACgS5XTfdjyBcuGku

Видео Working with Group By Extensions – ROLLUP,CUBE,GROUPING SETS канала Knowledge Amplifier