clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 756624 fiddles created (13585 in the last week).

CREATE TABLE sales ( id SERIAL PRIMARY KEY, location TEXT NOT NULL, product TEXT NOT NULL, quantity INTEGER NOT NULL )
 hidden batch(es)


INSERT INTO sales(location, product, quantity) VALUES ('London', 'Macbook', 10), ('London', 'iMac', 15), ('San Francisco', 'Macbook', 50), ('San Francisco', 'iMac', 70), ('San Francisco', 'Mac Pro', 40), ('Shanghai', 'iMac', 70), ('Shanghai', 'Macbook', 150);
7 rows affected
 hidden batch(es)


SELECT * FROM sales;
id location product quantity
1 London Macbook 10
2 London iMac 15
3 San Francisco Macbook 50
4 San Francisco iMac 70
5 San Francisco Mac Pro 40
6 Shanghai iMac 70
7 Shanghai Macbook 150
 hidden batch(es)


SELECT location, SUM(quantity) FROM sales GROUP BY ROLLUP(location);
location sum
405
Shanghai 220
San Francisco 160
London 25
 hidden batch(es)


SELECT location, product, SUM(quantity) FROM sales GROUP BY ROLLUP(location, product) ORDER BY location, product;
location product sum
London iMac 15
London Macbook 10
London 25
San Francisco iMac 70
San Francisco Macbook 50
San Francisco Mac Pro 40
San Francisco 160
Shanghai iMac 70
Shanghai Macbook 150
Shanghai 220
405
 hidden batch(es)


SELECT COALESCE(location, 'All locations') AS location, COALESCE(product, 'All products') AS product, SUM(quantity) FROM sales GROUP BY ROLLUP(location, product) ORDER BY location, product;
location product sum
All locations All products 405
London All products 25
London iMac 15
London Macbook 10
San Francisco All products 160
San Francisco iMac 70
San Francisco Macbook 50
San Francisco Mac Pro 40
Shanghai All products 220
Shanghai iMac 70
Shanghai Macbook 150
 hidden batch(es)


SELECT COALESCE(location, 'All locations') AS location, COALESCE(product, 'All products') AS product, SUM(quantity) FROM sales GROUP BY CUBE(location, product) ORDER BY location, product;
location product sum
All locations All products 405
All locations iMac 155
All locations Macbook 210
All locations Mac Pro 40
London All products 25
London iMac 15
London Macbook 10
San Francisco All products 160
San Francisco iMac 70
San Francisco Macbook 50
San Francisco Mac Pro 40
Shanghai All products 220
Shanghai iMac 70
Shanghai Macbook 150
 hidden batch(es)