So far, all SQL expressions have referred to the values in a single row at a time.
SELECT [columns] FROM [table] WHERE [expression];
An aggregate function in the [columns] clause computes a value from a group of rows.
Starting from this table of solar system objects, find the biggest:
SELECT MAX(mean_radius) FROM solar_system_objects;
See all SQLite aggregate functions.
An aggregate function also selects some row in the table to supply the values of columns
that are not aggregated. In the case of MAX or MIN,
this row is that of the MAX or MIN value.
SELECT body, MAX(mean_radius) FROM solar_system_objects;
SELECT body, MAX(surface_gravity) FROM solar_system_objects;
SELECT body, MIN(mean_radius) FROM solar_system_objects;
Otherwise, an individual value will just pick from an arbitrary row:
SELECT SUM(mass) FROM solar_system_objects;
Rows in a table can be grouped using GROUP BY, and aggregation is performed on each group.
SELECT [columns] FROM [table] GROUP BY [expression];
The number of groups is the number of unique values of an expression.
Based on this animals table, find the max weight per each number of legs:
SELECT legs, max(weight) FROM animals GROUP BY legs;
A HAVING clause filters the set of groups that are aggregated
SELECT [columns] FROM [table] GROUP BY [expression] HAVING [expression];
Find the weight/leg ratios that are shared by more than one kind of animal:
SELECT weight/legs, count(*) FROM animals
GROUP BY weight/legs HAVING COUNT(*) > 1;
Based on Marvel superheroes table, display the maximum intelligence, speed, and strength of each alignment (good/bad/neutral) and order by maximum intelligence.
SELECT alignment, MAX(intelligence) as max_int, MAX(speed), MAX(strength)
FROM marvels GROUP BY alignment ORDER BY max_int DESC;
What's the maximum difference between leg count for two animals with the same weight?
SELECT max(legs)-min(legs) AS diff FROM animals
GROUP BY weight ORDER BY -diff LIMIT 1;