SQL Aggregation

Tips for navigating the slides:
  • Press O or Escape for overview mode.
  • Visit this link for a nice printable version
  • Press the copy icon on the upper right of code blocks to copy the code

Class outline:

  • Aggregate functions
  • Groups

Aggregate functions

Aggregate functions

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.

Mixing aggregate functions & single values

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;


Grouping rows

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;

Filtering groups

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;

Exercise: Max speed

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;

Exercise: Leg count difference

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;