Header

Grouping with `GROUP BY`

Introduction

By now we know how to select all data from a table:

SELECT
  *
FROM
  movies;

id	name	year	rank
10920	Aliens	1986	8.2
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 36

With the function COUNT() we can calculate how many rows there are:

SELECT
  COUNT(*)
FROM
  movies;

count
36

Now it would be useful to calculate how many movies were released per year.

For this, we would need to count the number of rows for each unique year.
This can be done with GROUP BY.

1. `GROUP BY` example

We do this in the following example:

SELECT
  year,
  COUNT(*)
FROM
  movies
GROUP BY
  year;

year	count
1989	2
1991	1
1977	1
...	...

Columns: 2
Rows: 20

We select from the table movies the column year, and with COUNT(*) we count the rows.
With GROUP BY we group by the column year.
This shows us the number of movies released in each of the occurring years.
The new column is automatically named count.

If we don't add COUNT(*), we only see the unique years:

SELECT
  year
FROM
  movies
GROUP BY
  year;

year
1989
1991
1977
...

Columns: 1
Rows: 20

You must always specify what you do after grouping.

2. Grouping and other SQL statements

We now know that we can group with GROUP BY.

However, the result did not look very organized yet.

It would be nicer if we could sort by year or number of movies, for example.

Of course, this is possible.

We look at it in the following example:

SELECT
  year,
  COUNT(*)
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;

year	count
2005	1
2004	2
2003	4
...	...

Columns: 2
Rows: 20

ORDER BY can be easily added to apply sorting.

We can also sort by the number of movies per year:

SELECT
  year,
  COUNT(*)
FROM
  movies
GROUP BY
  year
ORDER BY
  count;

year	count
1977	1
1986	1
1972	1
...	...
1989	2
...	...
2001	3
...	...

Columns: 2
Rows: 20

This allows you to easily see in which years the most/fewest movies were made.

3. Giving a new column from `GROUP BY` a name with `AS`

We now know that we can group with GROUP BY.

By default, the column from COUNT(*) is named count.

With AS we can give the column a different name (alias).

We look at it in the following example:

SELECT
  year,
  COUNT(*) AS count_movies
FROM
  movies
GROUP BY
  year;

year	count_movies
1989	2
1991	1
1977	1
...	...

Columns: 2
Rows: 20

With AS we give the new column the name count_movies.

Note: in some SQL dialects you use the = sign instead of AS.

4. Filtering after grouping with `HAVING`

We have grouped by year.

This tells us the number of movies per year.

We know that we can filter with the WHERE statement.

Intuitively, we might come up with the following code:

SELECT
  year,
  COUNT(*) AS count_movies
FROM
  movies
GROUP BY
  year
WHERE
  COUNT(*) > 2;

However, this gives an error:

syntax error at or near "WHERE"

This error is due to the order in which SQL statements are executed.

WHERE is executed before GROUP BY. Therefore, you can only filter with WHERE on columns that are in the selected tables.
The column COUNT(*) as count_movies is not in the selected table but is created after GROUP BY.

Fortunately, there is a solution for this.

With HAVING you can filter after GROUP BY.

We look at this in the following example:

SELECT
  year,
  COUNT(*) AS count_movies
FROM
  movies
GROUP BY
  year
HAVING
  COUNT(*) > 2;

year	count_movies
2003	4
2000	4
2001	3
1999	4

Columns: 2
Rows: 4

This works as expected.
We now only see years where the number of movies from that year is greater than 2.

For the same reason that we cannot use WHERE, we also cannot use the new name (alias).

This is not yet known when the HAVING statement is executed.

This gives an error:

SELECT
  year,
  COUNT(*) AS count_movies
FROM
  movies
GROUP BY
  year
HAVING
  count_movies > 2;

column "count_movies" does not exist

Summary

You can group by unique values from a column.
- For this, you use GROUP BY.
- With COUNT(*) you can count the number of rows per unique value.
- For example:

SELECT
    year,
    COUNT(*)
FROM
    movies
GROUP BY
    year;

After grouping, you can, for example, sort with ORDER BY.
After grouping, you can give the new column a specific name (alias) with AS.
After grouping, you can filter with HAVING.
- For example:

SELECT
  year,
  COUNT(*) AS count_movies
FROM
  movies
GROUP BY
  year
HAVING
  COUNT(*) > 2;

Due to the order in which SQL statements are executed, the following does not work:
- Filtering on a column that is created by GROUP BY (such as COUNT(*)) with WHERE: you must use HAVING.
- Using an alias to sort or filter: you must use the original function, for example COUNT(*).

Grouping with GROUP BY