Header

Groeperen met `GROUP BY`

Introductie

Inmiddels weten we hoe we alle data uit een tabel moeten selecteren:

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

Met functie COUNT() kunnen we berekenen hoeveel rijen er zijn:

SELECT
  COUNT(*)
FROM
  movies;

count
36

Nu zou het handig zijn om bijvoorbeeld te berekenen hoeveel films er per jaartal zijn uitgekomen.

Daarvoor zouden we per uniek jaartal het aantal rijen moeten tellen.
Dit kan met GROUP BY.

1. `GROUP BY` voorbeeld

Dit doen we in het volgende voorbeeld:

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

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

Columns: 2
Rows: 20

We selecteren uit tabel movies kolom year, en met COUNT(*) tellen we de rijen.
Met GROUP BY groeperen we op kolom year.
Hierdoor zien we het aantal films wat uit elk van de voorkomende jaartallen.
De nieuwe kolom krijgt standaard de naam count.

Als we COUNT(*) niet toevoegen, zien we alleen de unieke jaartallen:

SELECT
  year
FROM
  movies
GROUP BY
  year;

year
1989
1991
1977
...

Columns: 1
Rows: 20

Je moet dus altijd benoemen wat je doet na het groeperen.

2. Groeperen en andere SQL statements

We weten nu dat we met GROUP BY kunnen groeperen.

Echter, het resultaat zag er nog niet direct erg overzichtelijk uit.

Het zou mooier zijn als er bijvoorbeeld op jaartal of aantal films gesorteerd kan worden.

Uiteraard kan dit.

We bekijken het in het volgende voorbeeld:

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 kan eenvoudig toegevoegd worden om een sortering toe te passen.

We kunnen ook op het aantal films per jaar sorteren:

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

Hiermee zie je eenvoudig in welke jaartallen de meeste/minste films zijn gemaakt.

3. Een nieuwe kolom vanuit `GROUP BY` een naam geven met `AS`

We weten nu dat we met GROUP BY kunnen groeperen.

Standaard krijgt de kolom vanuit COUNT(*) de naam count.

Met AS kunnen we de kolom een andere naam (alias) geven.

We bekijken het in het volgende voorbeeld:

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

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

Columns: 2
Rows: 20

Met AS geven we de nieuwe kolom de naam count_movies.

Let op: in sommige SQL dialecten gebruik je het = teken inplaats van AS.

4. Filteren na groeperen met `HAVING`

We hebben gegroepeerd op jaartal.

Hiermee weten we het aantal films per jaar.

We weten dat we met het WHERE statement kunnen filteren.

Intuitief zouden we daardoor op de volgende code kunnen komen:

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

Dit geeft echter een error:

syntax error at or near "WHERE"

Deze error komt door de volgorde waarin vanuit SQL statements uitgevoerd worden.

WHERE wordt voor GROUP BY uitgevoerd. Je kan met WHERE daarom alleen filteren op kolommen die in de geselecteerde tabellen zitten.
De kolom COUNT(*) as count_movies zit niet in de geselecteerde tabel maar onstaat pas na GROUP BY.

Gelukkig is hier een oplossing voor.

Met HAVING kun je wel na GROUP BY filteren.

We bekijken dit in het volgende voorbeeld:

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

Dit werkt zoals verwacht.
We zien nu alleen jaartallen waar het aantal films uit dat jaar groter is dan 2.

Om dezelfde reden dat we WHERE niet kunnen gebruiken, kunnen we ook de nieuwe naam (alias) niet gebruiken.

Deze is nog niet bekend als het HAVING statement uitgevoerd wordt.

Dit geeft een error:

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

column "count_movies" does not exist

Samenvatting

Je kunt op unieke waarden uit een kolom groeperen.
- Hiervoor gebruik je GROUP BY.
- Met COUNT(*) kun je het aantal rijen per unieke waarde tellen.
- Bijvoorbeeld:

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

Na groeperen kun je bijvoorbeeld sorteren met ORDER BY.
Na groeperen kun je de nieuwe kolom een specifieke naam (alias) geven met AS.
Na groeperen kun je filteren met HAVING.
- Bijvoorbeeld:

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

Door de volgorde waarin SQL statements uitvoert werkt het volgende niet:
- Filteren op een kolom die ontstaat door GROUP BY (zoals COUNT(*)) met WHERE: je moet HAVING gebruiken.
- Een alias gebruiken om te sorteren of filteren: je moet de originele functie gebruiken, bijvoorbeeld COUNT(*).

Groeperen met GROUP BY