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

data-creation.sql

-- 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');

query1.sql

SELECT year,
    COUNT(*) AS the_count,
    SUM(amount) AS the_sum
FROM purchases
GROUP BY year;

query2.sql

SELECT year,
    name,
    COUNT(*) AS the_count,
    SUM(amount) AS the_sum
FROM purchases
GROUP BY year, name;

query3.sql

SELECT year,
    name,
    SUM(amount) AS numerator,
    SUM(SUM(amount))
      OVER (PARTITION BY year) AS denominator
FROM purchases
GROUP BY year, name;

query4.sql

SELECT year,
    name,
    SUM(amount)
      * 100.0
      / SUM(SUM(amount)) OVER (PARTITION BY year)
      AS pct_of_spending
FROM purchases
GROUP BY year, name;