Group By. Hoe werkt het?
GROUP BY zorgt er voor dat de database intern de records indeelt in groepjes die dezelfde waarde voor een bepaalde kolom hebben. De resultaten worden vervolgens gegeven voor elke groep records.
Dit is extreem belangrijk om te onthouden: elk resultaat uit een query waar GROUP BY in is gebruikt wordt gegeven voor elk groepje records. Je kunt dus alleen waarden opvragen waarvan de database per groepje een zinnig antwoord kan ophalen, zoals de hoogste waarde, de gemiddelde waarde, etc.
GROUP BY, wanneer gebruik je dat nou?
Je gebruikt een GROUP BY statement enkel en alleen in een query op het moment dat je groepjes wilt gaan vormen. De regels in een groepje staan bij elkaar omdat ze dezelfde eigenschappen hebben. Een veel gebruikte toepassing is bij de 'aggregate' functies van SQL, zoals bijvoorbeeld SUM, AVG, MIN, MAX en COUNT.
Voor de complete lijst van de MySQL aggregate functies zie: GROUP BY-functies van MySQL
Een voorbeeld:
Je bent eigenaar van een autoverhuurbedrijf en je hebt de volgende auto's te huur:
Huur auto's
2
3
4
5
6
7
1 Opel Astra 200
2 Audi A4 300
3 BMW Z3 1000
4 Opel Vectra 250
5 Audi A8 500
6 Opel Omega 345
Nu ben je ge?ntereseerd in wat per merk de duurste auto is om te huren, met andere woorden: we gaan groepjes maken!
Huur auto's gegroepeerd op merk
2
3
4
5
6
7
8
9
1 Opel Astra 200
4 Opel Vectra 250
6 Opel Omega 345
2 Audi A4 300
5 Audi A8 500
3 BMW Z3 1000
De auto's zijn nu gegroepeerd op het merk. In SQL doe je dit met "GROUP BY merk". -let wel: intern doet je databas zo iets. Bovenstaande tabel wordt met een ORDER BY gegenereerd. Bedoeld wordt dat een group by in de query de data als hierboven organiseert.- Voor het ophalen van de hoogste verhuurprijs, gebruik je de functie MAX(). Deze functie gaat dus per groepje (Let op: per groepje!) de hoogste prijs bepalen.
De query "SELECT merk, MAX(huurprijs) FROM auto GROUP BY merk" zal dus het volgende resultaat teruggeven:
2
3
4
5
Merk MAX(huurprijs)
Opel 345
Audi 500
BMW 1000
Maar dat hoeft toch niet altijd?
Inderdaad laat MySQL in de standaard settings toe, dat je kolommen selecteert die je niet in het GROUP BY deel van je query vermeldt. Stel dat we voortbouwend op het vorige voorbeeld ook willen weten welk type auto dat dan is.
De query zou dan bijvoorbeeld het volgende (foute!) kunnen worden:
Merk op dat de kolom "type" w?l in het select-stuk van de query staat, maar niet in het GROUP BY-stuk
MySQL geeft dan keurig het antwoord.
2
3
4
5
Merk Type MAX(huurprijs)
Opel Astra 345
Audi A4 500
BMW Z3 1000
Het resultaat ziet er keurig uit, geen foutmelding van MySQL. Wat wil je nog meer? Nou, bijvoorbeeld zinnige resultaten: de Astra kost bijvoorbeeld maar 200 en de prijs van de A4 klop ook niet. Mysql heeft je foutieve query dus beantwoord met een willekeurige rij voor het type. Toevallig was dat nu de Astra, maar waren de records in een andere volgorde ingevoerd dan had daar ook het juiste type kunnen staan (voor je test-case lijkt dan alles goed), terwijl dat niet meer dan stom toeval was geweest.
Wees er dus altijd op bedacht dat je echt alle niet aggregate-kolommen uit het SELECT deel moet vermelden in je GROUP BY-deel.
In dit geval betekent dat wel, dat de query ofwel gewoon alle records terug geeft, ofwel je er in moet berusten dat het gestelde probleem (wat is het duurste type per merk) niet in 1 query (zonder subquery) op te lossen is.
Of nog beter: configureer MySQL zo, dat het dit soort foute queries niet accepteert