A nested aggregation refers to an aggregation on top of another aggregation. Using a group by arranges it so the aggregation is only applied to part of the table, grouped by the group-by criteria.
For example, an example of a group-by is getting the minimum age from users birthdates, grouped by city & country. This returns a table of minimum ages from each location.
An example of a nested aggregation is getting the minimum age from each location, and then finding the average of those ages.
To see the queries in action, try our demo below:
Select a query for the nested aggregation on the left first! 👈
To get the minimum age from birthdates, grouped by the location (city, country), we use this SQL query:
SELECT city, country, MIN (EXTRACT (YEAR FROM (AGE (birthdate)))) FROM users, location WHERE users.born_city=location.city AND users.born_country=location.country GROUP BY city, country;
By replacing MIN with AVG, SUM, MAX, or COUNT, we can get the different results for the card on the left.
To calculate the average, minimum, and maximum of this query, we used this command:
SELECT AVG(location_data), MIN(location_data), MAX(location_data) FROM (SELECT city, country, MIN (EXTRACT (YEAR FROM (AGE (birthdate)))) AS location_data FROM users, location WHERE users.born_city=location.city AND users.born_country=location.country GROUP BY city, country) users, location;
Notice that we used an AS statement to save the data from the subquery to be used in the calculations of the overall average, minimum, and maximum. The results come out as columns in the result table.