Skip to content

Instantly share code, notes, and snippets.

@jdw1996
Last active Mar 25, 2018
Embed
What would you like to do?
Code snippets used in my blog post on nesting aggregate functions in SQL: https://jdw1996.github.io/2017-03-26-nesting-sql-aggregate-functions.html
-- Create the table to hold our data.
CREATE TABLE purchases (
name VARCHAR(5),
amount DECIMAL(5,2),
year CHAR(4)
);
-- Insert the example data.
INSERT INTO purchases VALUES ( 'Gene', 234.04, '2015');
INSERT INTO purchases VALUES ( 'Paul', 56.99, '2015');
INSERT INTO purchases VALUES ('Peter', 23.95, '2015');
INSERT INTO purchases VALUES ('Peter', 102.76, '2015');
INSERT INTO purchases VALUES ( 'Ace', 5.49, '2016');
INSERT INTO purchases VALUES ( 'Ace', 19.41, '2016');
INSERT INTO purchases VALUES ( 'Ace', 22.77, '2016');
INSERT INTO purchases VALUES ( 'Gene', 166.30, '2016');
INSERT INTO purchases VALUES ( 'Paul', 65.99, '2016');
INSERT INTO purchases VALUES ('Peter', 89.90, '2016');
SELECT year,
COUNT(*) AS the_count,
SUM(amount) AS the_sum
FROM purchases
GROUP BY year;
SELECT year,
name,
COUNT(*) AS the_count,
SUM(amount) AS the_sum
FROM purchases
GROUP BY year, name;
SELECT year,
name,
SUM(amount) AS numerator,
SUM(SUM(amount))
OVER (PARTITION BY year) AS denominator
FROM purchases
GROUP BY year, name;
SELECT year,
name,
SUM(amount)
* 100.0
/ SUM(SUM(amount)) OVER (PARTITION BY year)
AS pct_of_spending
FROM purchases
GROUP BY year, name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment