Skip to content

Instantly share code, notes, and snippets.

@DominikStyp

DominikStyp/example_table.sql

Created August 16, 2021 20:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save DominikStyp/29ad6060c2996f6223a2b29a72676367 to your computer and use it in GitHub Desktop.
Save DominikStyp/29ad6060c2996f6223a2b29a72676367 to your computer and use it in GitHub Desktop.
Download ZIP
SQL GROUP BY WITH ROLLUP: Grouping by multiple columns and rolling up to one
Raw
example_table.sql
CREATE TABLE test (
id INT PRIMARY KEY AUTO_INCREMENT,
year SMALLINT,
month TINYINT,
revenue INT
);
INSERT INTO test (year, month, revenue) VALUES
(2020, 01, 900),
(2020, 02, 1500),
(2021, 01, 1000),
(2021, 02, 1200),
(2022, 01, 1000),
(2022, 02, 1300);
Raw
expected_result.csv
year month sum(revenue)
2020 1 900
2020 2 1500
2020 null 2400
2021 1 1000
2021 2 1200
2021 null 2200
2022 1 1000
2022 2 1300
2022 null 2300
null null 6900
Raw
group_with_rollup.sql
SELECT year, month, sum(revenue)
FROM test
GROUP BY year, month WITH ROLLUP;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment