Aggregeren van data met aggregate functions in SQL

Als je data uit een relationele database haalt met een SQL query, kan het wenselijk zijn om op deze gegevens een aggregate function toe te passen. Er zijn verschillende aggregate functies beschikbaar binnen SQL, voorbeelden zijn: COUNT(), SUM(), AVG(), MIN(), en MAX(). Hier zullen we uitgebreid op ingaan in dit blog.

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

Deze blog zal de volgende structuur aanhouden:

• Introductie
• COUNT(): het aantal rijen
• SUM(): de som van de waarden in een kolom
• AVG(): het gemiddelde
• MIN(): het kleinste getal
• MAX(): het grootste getal
• Samenvatting SQL aggregate functions

Introductie in SQL aggregate functions

Om alles wat op deze pagina staat te kunnen begrijpen is voorkennis van SQL vereist. We veronderstellen dat je het volgende weet:

• Hoe je met GROUP BY gegevens groepeert. Als je dit niet weet kun je hier meer leren over GROUP BY
• Hoe je met HAVING gegevens kunt filteren nadat deze gegroepeert zijn. Ook daarover vind je hier meer informatie.
• Hoe je met de COUNT functie het aantal rijen kunt tellen. In deze blog leggen we uit hoe COUNT werkt

Wanneer je bovenstaande onder de knie hebt, is onderstaande SQL query goed te volgen:

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

Columns: 2
Rows: 20

Hierbij is de COUNT() functie een aggregate function. COUNT() telt het aantal rijen. Aggregeren is een ander woord voor groeperen. Naast COUNT zijn er ook andere aggregatiefuncties.

Veelgebruikte aggregatiefuncties zijn:

• COUNT(): telt het aantal rijen.
• SUM(): berekent de som van waarden in een numerieke kolom.
• AVG(): berekent het gemiddelde van waarden in een numerieke kolom.
• MIN(): geeft het kleinste getal van waarden in een numerieke kolom.
• MAX(): geeft het grootste getal van waarden in een numerieke kolom.

We gaan deze aggregatiefuncties één voor één bekijken.

1. COUNT(): het aantal rijen

Onderstaand voorbeeld met COUNT() hebben we eerder gezien.

Met GROUP BY groeperen we op year. Met aggregatiefunctie COUNT(*) tellen we het aantal rijen. Zo ontdekken we het aantal films per jaar.

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

Columns: 2
Rows: 20

2. SUM(): de som van waarden in een kolom

SUM() berekent de som van waarden in een numerieke kolom.

We passen dit toe in onderstaand voorbeeld.

We starten weer met groeperen. Met GROUP BY groeperen we op year. Vervolgens gebruiken we twee aggregatiefuncties, namelijk COUNT(*) en SUM(rank). Zo tellen we niet alleen het aantal films, maar tellen we ook de scores van films uit een bepaald jaar bij elkaar op.

SELECT
  year,
  COUNT(*) AS movies_count,
  SUM(rank) AS rank_sum
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;

Columns: 3
Rows: 20

Blijkbar zijn er in 2005 geen films uitgekomen met een waarde in de 'rank' kolom, waardoor de output (NULL) teruggeeft.

3. AVG(): het gemiddelde

AVG() berekent het gemiddelde van waarden in een numerieke kolom.

We breiden bovenstaand voorbeeld verder uit. Groeperen met GROUP BY op year.

We passen nu drie aggregate functions toe:

• Met COUNT(*) tellen we het aantal rijen.
• Met SUM(rank) tellen we het de scores van films uit een bepaald jaar bij elkaar op.
• Met AVG(rank) berekenen we de gemiddelde score per jaar.

SELECT
  year,
  COUNT(*) AS movies_count,
  SUM(rank) AS rank_sum,
  AVG(rank) AS rank_avg
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;

Columns: 4
Rows: 20

4. MIN(): het kleinste getal

MIN() geeft het kleinste getal van waarden in een numerieke kolom.

We breiden bovenstaand voorbeeld verder uit. Wederom groeperen we met GROUP BY op year.

We passen nu vier aggregate functions toe:

• Met COUNT(*) tellen we het aantal rijen.
• Met SUM(rank) tellen we het de scores van films uit een bepaald jaar bij elkaar op.
• Met AVG(rank) berekenen we de gemiddelde score per jaar.
• Met MIN(rank) krijgen we de laagste score per jaar.

SELECT
  year,
  COUNT(*) AS movies_count,
  SUM(rank) AS rank_sum,
  AVG(rank) AS rank_avg,
  MIN(rank) AS rank_min
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;

Columns: 5
Rows: 20

5. MAX(): het grootste getal

MAX() geeft het grootste getal van waarden in een numerieke kolom.

We breiden bovenstaand voorbeeld verder uit. Wederom groeperen we met GROUP BY op year.

We passen nu vier aggregate functions toe:

• Met COUNT(*) tellen we het aantal rijen.
• Met SUM(rank) tellen we het de scores van films uit een bepaald jaar bij elkaar op.
• Met AVG(rank) berekenen we de gemiddelde score per jaar.
• Met MIN(rank) krijgen we de laagste score per jaar.
• Met MAX(rank) krijgen we de hoogste score per jaar.

SELECT
  year,
  COUNT(*) AS movies_count,
  SUM(rank) AS rank_sum,
  AVG(rank) AS rank_avg,
  MIN(rank) AS rank_min,
  MAX(rank) AS rank_max
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;

Columns: 6
Rows: 20

Samenvatting

• Met GROUP BY kun je op een of meerdere kolommen groeperen.
• Met HAVING kun je (na groeperen) op een of meerdere kolommen filteren.
• Er zijn meerdere aggregatiefuncties:
  • COUNT(): telt het aantal rijen.
  • SUM(): berekent de som van waarden in een numerieke kolom.
  • AVG(): berekent het gemiddelde van waarden in een numerieke kolom.
  • MIN(): geeft het kleinste getal van waarden in een numerieke kolom.
  • MAX(): geeft het grootste getal van waarden in een numerieke kolom.
• Voorbeeld:

SELECT
  year,
  COUNT(*) AS movies_count,
  SUM(rank) AS rank_sum,
  AVG(rank) AS rank_avg
FROM
  movies
GROUP BY
  year
ORDER BY
  year DESC;

• Er zijn nog meer aggergatiefuncties dan die hier zijn getoond. Dit is tevens afhankelijk van het SQL dialect dat je gebruikt. Hier vind je een voorbeeld voor aggregate functions die beschikbaar zijn binnen MS SQL Server.

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

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.