Discussion on: #SQL30 Day 7: Grouping Sets

[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!