Groeperen met SQL GROUP BY

Iedereen die met data werkt zal vroeg of laat data uit een database willen groeperen. Vaak gebruikt men een relationele database die met SQL statements (ook wel SQL query) bevraagd kan worden. Met het GROUP BY statement kan de opgevraagde data direct gegroepeerd worden.

In dit blog gaan we in op het SQL GROUP BY statement, waarmee je uit een database opgehaalde data direct op een door jou gewenste manier kunt groeperen.

Wil je op je eigen computer meedoen met deze tutorial? Volg dan deze stappen om SQL en PgAdmin te installeren en de IMDb-database in te laden.

Deze blog zal de volgende structuur aanhouden:

• Introductie
• GROUP BY voorbeeld
• Groeperen en andere SQL statements
• Een nieuwe kolom vanuit GROUP BY een naam geven met AS
• Filteren na groeperen met HAVING
• Samenvatting van SQL GROUP BY

Introductie

Om deze blog te kunnen volgen dien je te weten hoe je met SELECT en FROM data ophaalt uit een SQL tabel uit een database. Dit blog behandelt SELECT en FROM volledig.

SELECT
  *
FROM
  movies;

Columns: 4
Rows: 36

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

SELECT
  COUNT(*)
FROM
  movies;

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 SQL GROUP BY.

1. GROUP BY voorbeeld

Dit doen we in het volgende voorbeeld:

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

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. Dit laten we hier onder zien voor de volledigheid.

SELECT
  year
FROM
  movies
GROUP BY
  year;

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. Daar gaan we nu wat aan doen.

Zo zou het 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;

Columns: 2
Rows: 20

SQL 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;

Columns: 2
Rows: 20

Hiermee zie je eenvoudig in welke jaartallen de meeste of minste films zijn gemaakt, afhankelijk van hoe je sorteert. Je merkt al dat GROUP BY een enorm krachtig statement is waarmee je veel informatie naar boven kunt halen.

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

We weten nu dat we met GROUP BY gegevens kunnen groeperen.

In bovenstaand voorbeeld hebben we gezien dat standaard de kolom vanuit COUNT(*) de naam count krijgt. Dit kan wat onoverzichtelijk zijn, omdat niet altijd direct duidelijk is wát er geteld wordt in de kolom.

Met AS kunnen we de kolom een andere naam (alias) geven. Zo kunnen we duidelijker maken welke informatie een kolom bevat.

We bekijken het in het volgende voorbeeld:

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

Columns: 2
Rows: 20

Met AS geven we de nieuwe kolom de naam count_movies. Het is nu duidelijk dat het over aantallen films gaat.

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

Ook interessant: volledige uitleg over SQL varianten incl verschillen in syntax, clients & licentiemodel

4. Filteren na groeperen met HAVING

In bovenstaande voorbeelden hebben we GROUP BY gebruikt om te groeperen op jaartal.

Hiermee weten we het aantal films per jaar.

Waarschijnlijk weet je ook dat je normaal met het WHERE statement kunt filteren in SQL.

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 namelijk voor GROUP BY uitgevoerd. Hierdoor kun je WHERE niet gebruiken om na GROUP BY te filteren.

Leer er hier alles over: overzicht uitvoervolgorde SQL statements

Gelukkig is hier een oplossing voor. Met het HAVING statement 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;

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  

De oplossing hiervoor is om de originele functie te gebruiken, in ons geval COUNT(*).

Samenvatting van SQL GROUP BY

• 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 met WHERE, je moet HAVING gebruiken.
  • Een alias gebruiken om te sorteren of filteren: je moet de originele functie gebruiken, bijvoorbeeld COUNT(*).

Wil je nog veel meer leren over SQL? Bekijk dan onze SQL cursus voor data analyse of download onderstaande opleidingsbrochure. In onze SQL cursus wisselen theorie en praktijkoefening elkaar steeds af. Nadat je deze training hebt gevolgd kun je zelfstandig met SQL werken. Je kunt ook altijd even contact opnemen via info@datasciencepartners.nl of 020 - 24 43 146 als je een vraag hebt.