GROUP BY en HAVING gebruiken in SQL

We gebruiken de Unicorn Companies Database, die beschikbaar is op DataLab, DataCamps AI-aangedreven datanotebook. Deze bedrijven heten Unicorns omdat het startups zijn met een waardering van meer dan een miljard dollar. Voor de eenvoud focussen we op drie tabellen: companies, sales en product_emissions.  Wil je alle voorbeeldcode uit deze tutorial zelf draaien, dan kun je gratis een DataLab-werkboek aanmaken met de database en alle codevoorbeelden al voor je geladen.

SQL GROUP BY gebruiken

GROUP BY is een veelgebruikt SQL-commando om data te aggregeren en er inzichten uit te halen. Er zijn drie fases wanneer je data groepeert:

\n
• Splitsen: de dataset wordt opgesplitst in delen met rijen op basis van de waarden van de variabelen die we voor de aggregatie hebben gekozen
\n
• Toepassen: een aggregatiefunctie berekenen, zoals gemiddelde, minimum en maximum, die één enkele waarde retourneert
\n
• Combineren: al deze resulterende outputs worden samengevoegd in één tabel. Zo hebben we één waarde voor elke modaliteit van de variabele van interesse.
\n

SQL GROUP BY Voorbeeld 1

We beginnen met een simpel voorbeeld van GROUP BY. Stel dat we de top tien landen willen vinden met het hoogste aantal Unicorn-bedrijven. 

SELECT * \nFROM companies

Het is ook fijn om de resultaten aflopend te sorteren op basis van het aantal bedrijven

SELECT country, COUNT(*) AS n_companies\nFROM companies\nGROUP BY country\nORDER BY n_companies DESC\nLIMIT 10

Hier zie je de resultaten. Het zal je waarschijnlijk niet verbazen dat de VS, China en India in de ranking staan. Laten we de keuzes achter deze query uitleggen: 

\n
• \n

  Merk eerst op dat we COUNT(*) hebben gebruikt om de rijen per groep te tellen, wat overeenkomt met het land. Daarnaast hebben we een SQL-alias gebruikt om de kolom een duidelijkere naam te geven. Dit kan met het sleutelwoord AS, gevolgd door de nieuwe naam. COUNT wordt uitgebreider behandeld in de tutorial COUNT() SQL FUNCTION.

  \n
\n
• \n

  De velden zijn geselecteerd uit de tabel companies, waar elke rij overeenkomt met een Unicorn-bedrijf. 

  \n
\n
• \n

  Daarna moeten we de kolomnaam na GROUP BY specificeren om de data op land te aggregeren. 

  \n
\n
• \n

  ORDER BY is nodig om de landen in de juiste volgorde te tonen, van het hoogste naar het laagste aantal bedrijven. 

  \n
\n
• \n

  We beperken de resultaten tot 10 met LIMIT, gevolgd door het aantal rijen dat je in de resultaten wilt.

  \n
\n

SQL GROUP BY Voorbeeld 2

Nu analyseren we de tabel met de verkopen. Voor elk ordernummer hebben we het type klant, de productlijn, de hoeveelheid, de eenheidsprijs, het totaal, enzovoort.

Dit keer zijn we geïnteresseerd in het vinden van de gemiddelde prijs per eenheid, het totale aantal orders en de totale opbrengst per productlijn:

SELECT \n    product_line,\n    AVG(unit_price) AS avg_price,\n    SUM(quantity) AS tot_pieces,\n    SUM(total) AS total_gain\nFROM sales\nGROUP BY product_line\nORDER BY total_gain DESC

\n
• \n

  In plaats van het aantal rijen te tellen, gebruiken we de functie AVG() om de gemiddelde prijs te krijgen en de functie SUM() om het totale aantal orders en de totale opbrengst per productlijn te berekenen. 

  \n
\n
• \n

  Zoals eerder geven we de kolom op die de dataset in eerste instantie opdeelt in stukken. Vervolgens zorgen de aggregatiefuncties ervoor dat we één rij per modaliteit van de productlijn krijgen. 

  \n
\n
• \n

  Dit keer is ORDER BY optioneel. Het is opgenomen om te laten zien hoe hogere totale opbrengsten niet altijd evenredig zijn aan hogere gemiddelde prijzen of aantallen stuks. 

  \n
\n

De beperkingen van WHERE

Neem het vorige voorbeeld nog eens. Nu willen we een voorwaarde toevoegen aan de query: we willen alleen filteren op een totaal aantal orders hoger dan 40.000. Laten we de WHERE-clausule proberen:

SELECT \n    product_line,\n    AVG(unit_price) AS avg_price,\n    SUM(quantity) AS tot_pieces,\n    SUM(total) AS total_gain\nFROM sales\nWHERE SUM(total) > 40000\nGROUP BY product_line\nORDER BY total_gain DESC

Deze query geeft de volgende fout terug:

Deze fout ontstaat omdat het niet mogelijk is aggregatiefuncties in de WHERE-clausule te gebruiken. We hebben een nieuw commando nodig om dit op te lossen.

SQL HAVING gebruiken

Net als WHERE filtert de HAVING-clausule de rijen van een tabel. Waar WHERE probeerde de hele tabel te filteren, filtert HAVING rijen binnen elk van de groepen die door GROUP BY zijn gedefinieerd

SQL HAVING Voorbeeld 1

Hier is het vorige voorbeeld opnieuw, waarbij we het woord WHERE hebben vervangen door HAVING.

SELECT \n    product_line,\n    AVG(unit_price) AS avg_price,\n    SUM(quantity) AS tot_pieces,\n    SUM(total) AS total_gain\nFROM sales\nGROUP BY product_line\nHAVING SUM(total) > 40000\nORDER BY total_gain DESC

Dit keer levert het drie rijen op. De andere productlijnen voldeden niet aan het criterium, dus gingen we van zes resultaten naar drie. 

Wat valt je verder op aan de query? We hebben de kolomalias niet doorgegeven aan HAVING, maar de aggregatie van het oorspronkelijke veld. Vraag je je af waarom? In het volgende voorbeeld ontrafel je het mysterie.

SQL HAVING Voorbeeld 2

Als laatste voorbeeld gebruiken we de tabel product_emissions, die de uitstoot van de producten van de bedrijven bevat.

Dit keer willen we de gemiddelde product carbon footprint (pcf) per bedrijf tonen dat behoort tot de industry group “Technology Hardware & Equipment”. Daarnaast is het handig om het aantal producten per bedrijf te zien om te begrijpen of er een relatie is tussen het aantal producten en de carbon footprint. We gebruiken opnieuw HAVING om bedrijven te selecteren met een gemiddelde carbon footprint van boven de 100.

SELECT pe.company, count(product_name) AS n_products, avg(carbon_footprint_pcf) AS avg_carbon_footprint_pcf\nFROM product_emissions AS pe\nWHERE industry_group = 'Technology Hardware & Equipment'\nGROUP BY pe.company, industry_group\nhaving avg_carbon_footprint_pcf>100\nORDER BY n_products

Er verscheen een fout nadat we probeerden de alias te gebruiken. Voor de HAVING-clausule bestaat de naam van de nieuwe kolom niet, dus kan de query niet worden gefilterd. Laten we de aanvraag corrigeren:

SELECT pe.company, count(product_name) AS n_products, avg(carbon_footprint_pcf) AS avg_carbon_footprint_pcf\nFROM product_emissions AS pe\nWHERE industry_group = 'Technology Hardware & Equipment'\nGROUP BY pe.company, industry_group\nhaving avg(carbon_footprint_pcf)>100\nORDER BY n_products

Dit keer werkte de voorwaarde, en we kunnen de resultaten uit de tabel visualiseren. We hebben zojuist geleerd dat kolomaliassen niet in HAVING kunnen worden gebruikt omdat deze voorwaarde wordt toegepast vóór de SELECT. Daarom kan het de velden met de nieuwe namen niet herkennen.

SQL-volgorde van uitvoering

Dit is de volgorde van de commando’s tijdens het schrijven van de query:

SELECT\nFROM\nWHERE\nGROUP BY\nHAVING\nORDER BY

Maar er is een vraag die je jezelf moet stellen. In welke volgorde worden SQL-commando’s uitgevoerd? Als mensen nemen we vaak aan dat de computer SQL van boven naar beneden leest en interpreteert. Maar de realiteit is anders dan het lijkt. Dit is de juiste uitvoeringsvolgorde:

FROM\nWHERE\nGROUP BY\nHAVING\nSELECT\nORDER BY\nLIMIT

De queryprocessor begint dus niet bij SELECT, maar begint met het selecteren van welke tabellen moeten worden opgenomen, en SELECT wordt uitgevoerd na HAVING. Dit verklaart waarom HAVING het gebruik van ALIAS niet toestaat, terwijl ORDER BY daar geen probleem mee heeft. Daarnaast verduidelijkt deze uitvoeringsvolgorde waarom HAVING samen met GROUP BY wordt gebruikt om voorwaarden toe te passen op geaggregeerde data, terwijl WHERE dat niet kan. 

Ik raad je aan onze tutorial SQL Order of Execution: Understanding How Queries Run te lezen, die hier dieper op ingaat, als je meer wilt leren.

Til het naar een hoger niveau

Na het lezen van deze tutorial heb je als het goed is een duidelijk beeld van het verschil tussen GROUP BY en HAVING. Je kunt oefenen in DataLab om deze concepten onder de knie te krijgen.

Wil je door naar het volgende niveau in het SQL-leerpad, dan kun je onze cursus Intermediate SQL volgen. Moet je je basis in SQL nog aanscherpen, ga dan terug naar de cursus Introduction to SQL om de fundamenten van de taal te leren.