DEV Community

Cover image for #SQL30 Day 7: Grouping Sets

#SQL30 Day 7: Grouping Sets

zchtodd on October 09, 2019

Welcome to the SQL showdown series! What is this and how does it work? I'm committing to publishing a SQL challenge every day for 30 ...
Collapse
 
smason profile image
Sam Mason

couple of variants here, one using old style GROUP BY statements:

(
  SELECT city, product_line,
    date_part('year', sale_date)::INT AS year, date_part('month', sale_date)::INT AS month,
    SUM(total) AS sales
  FROM supermarket
  GROUP BY 1, 2, 3, 4
UNION ALL
  SELECT city, product_line,
    date_part('year', sale_date)::INT, NULL,
    SUM(total) AS sales
  FROM supermarket
  GROUP BY 1, 2, 3
)
ORDER BY 1, 2, 3, 4;

and a version (after actually googling about GROUPING SETS) that probably does what you were expecting:

SELECT city, product_line,
  date_part('year',  sale_date)::INT AS year,
  date_part('month', sale_date)::INT AS month,
  SUM(total) AS sales
FROM supermarket
GROUP BY GROUPING SETS (
  (1, 2, 3),
  (1, 2, 3, 4)
)
ORDER BY 1, 2, 3, 4;

be interesting to see if you did anything different!