sql aggregate functions uitleg voorbeeld tutorial

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 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:

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;
yearcount
20051
20042
20034
......
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.


Leren werken met SQL voor data analayse? Na onze 2-daagse SQL cursus kun je volledig zelfstandig werken met SQL.

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;
yearmovies_count
20051
20042
20034
......
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;
yearmovies_countrank_sum
20051(NULL)
2004216.5
2003424.5
.........
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;
yearmovies_countrank_sumrank_avg
20051(NULL)(NULL)
2004216.58.25
2003424.58.166666666666666
............
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;
yearmovies_countrank_sumrank_avgrank_min
20051(NULL)(NULL)(NULL)
2004216.58.258.2
2003424.58.1666666666666668
...............
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;
yearmovies_countrank_sumrank_avgrank_minrank_max
20051(NULL)(NULL)(NULL)(NULL)
2004216.58.258.28.3
2003424.58.16666666666666688.4
..................
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.

by: