SQL 101: Aggregates On Aggregates On Aggregates

A Beginner's Guide to SQL Aggregate Functions

Photo by Luke Chesser on Unsplash

Aggregates are one of the most powerful SQL features. They are integral to data analysis and are pretty fun to write!

In the SQL world, aggregates are functions applied to a group of rows to retrieve a single "aggregate" value. For example, let's say you have rows of high-score data that look like this, and you want to write a query that identifies the maximum high score.

Sample High Score Data

To do this, you can use the max() aggregate function. Here's how that works:

select max(score) from high_scores;
-- returns 9409

You can use other aggregate functions in the same way. The most common aggregate functions in addition to max() are min(), avg(), sum(), and count().

select min(score) from high_scores;
-- returns 2441
select avg(score) from high_scores;
-- returns 6276
select sum(score) from high_scores;
-- returns 50211
select count(score) from high_scores;
-- returns 8

Partitioning Data with GROUP BY