Advanced

Counting per group

I wanted a count for each category, so I ran one query per category and pasted the numbers into a note. GROUP BY does all those counts in a single query.

What you will see

role|n dev|2 lead|1 sqlite>

About 2 min of reading

Try It

Add a category column, then count per category in one query.

Draw a table with a role column so there is something to group by.

CREATE TABLE crew (name TEXT, role TEXT);

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Fill it with rows that share roles.

INSERT INTO crew (name, role) VALUES ("Ada", "dev"), ("Linus", "dev"), ("Guido", "lead");

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Count per role. Two rows are dev, one is lead, so you get two lines.

SELECT role, COUNT(*) FROM crew GROUP BY role;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

role|count dev|2 lead|1 sqlite>

Name the count and order the groups by it. The busiest role comes first.

SELECT role, COUNT(*) AS n FROM crew GROUP BY role ORDER BY n DESC;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

role|n dev|2 lead|1 sqlite>

Data Flow

GROUP BY buckets the rows by a column, an aggregate runs once per bucket, and each bucket returns one line.

1 Rows arrive from FROM

↓

2 GROUP BY buckets them

↓

3 Aggregate runs per bucket

↓

4 One line per bucket

Why this works

GROUP BY splits the rows

GROUP BY takes the rows and sorts them into buckets by a column. An aggregate then runs once per bucket instead of once over everything. So COUNT(*) GROUP BY city is not one total, it is a count for each city. The column you group by is the thing you are counting by.

Only group columns and aggregates

In a grouped query, every column in SELECT must be either grouped or inside an aggregate. If you write SELECT name, COUNT(*) GROUP BY city, the name is ambiguous, one city has many names, and the database has to pick one. That is a trap: it may run and hand you a name that means nothing. Group by what you want per line, and aggregate the rest.

Examples

One number per group

SELECT city, COUNT(*) FROM users JOIN cities ON users.id = cities.id GROUP BY city;

GROUP BY says which column defines a group. The rows are bucketed by city, and COUNT runs once per bucket. You get one line per city.

Name the answer

SELECT city, COUNT(*) AS n FROM users GROUP BY city;

AS n renames the count column in the answer. Without it the header is a bare count. A name makes the output readable when you read it later.

Order the groups

SELECT city, COUNT(*) AS n FROM users GROUP BY city ORDER BY n DESC;

You can sort on the computed count. This lists the busiest group first, which is the shape you usually want when reporting.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Putting rows in order

Previous Lesson

Putting rows in order

Next Lesson

Computing inside the query

Computing inside the query →