SQL Basis

Hoofdstuk 10 - Gegevens groeperen

  • Stel, we willen per leverancier (vend_id) weten hoeveel verschillende producten ze kunnen leveren.
SELECT vend_id,
       COUNT(*) AS AantalProducten
FROM   Products;

Error Code: 1140. In aggregated query without GROUP BY, expression

  • Dit is een 'scheve query'! Deze query zou meerdere vend_id's en maar één totaal aantal rijen opleveren. Dit gaat fout!
  • Aggregaatfuncties combineren met velden die meer dan één waarde op leveren kan met de clausule 'GROUP BY'
  • Met de GROUP BY worden rijen gegroepeerd zodat deze als groep bekeken kunnen worden
  • Met de GROUP BY kan je aggregatiefuncties toepassen op een subset van rijen
SELECT   vend_id,
         COUNT(*) AS AantalProducten
FROM     Products
GROUP BY vend_id;
vend_idAantalProducten
BRS013
DLL014
FNG012

  • Er kunnen meerdere kolommen in een GROUP BY worden opgegeven
  • Elke 'gewone' kolom in de SELECT (met meerdere velden) kun je het beste in de GROUP BY zetten
  • Aliassen kunnen niet worden gebruikt in een GROUP BY

Selecteer uit OrderItems het prod_id en het totaal aantal bestelde artikelen hiervan

-- Oracle / MySQL / SQL Server
SELECT   prod_id,
         SUM(quantity) AS TotaalBesteld	
FROM     OrderItems
GROUP BY prod_id;
prod_idTotaalBesteld
BNBG01360
BNBG02360
BNBG03360
BR01120
BR0210
BR03165
RGAN0155
  • Groepen die met GROUP BY zijn gemaakt kunnen niet met WHERE gefilterd worden
  • Groepen worden gefilterd met HAVING
  • WHERE en HAVING hebben een soortgelijke werking maar:
    • WHERE filtert rijen
    • HAVING filtert groepen
SELECT   vend_id,
         COUNT(*) AS AantalProducten
FROM     Products
GROUP BY vend_id
HAVING   COUNT(*) < 4;
vend_idAantalProducten
BRS013
FNG012


Selecteer de klant-id's die 2 of meer Orders hebben geplaatst

-- Oracle / MySQL / SQL Server
SELECT   cust_id,
         COUNT(*) AS AantalOrders
FROM     Orders
GROUP BY cust_id
HAVING   COUNT(*) >= 2;
cust_idAantalOrders
10000000012


  • Soms is het nodig om een WHERE filter + een HAVING filter te gebruiken

Selecteer alle leveranciers die 2 of meer producten hebben met een prijs van 4 of meer

-- Oracle / MySQL / SQL Server
SELECT   vend_id,
         COUNT(*) AS AantalProducten
FROM     Products
WHERE    prod_price >= 4
GROUP BY vend_id
HAVING   COUNT(*) >= 2;
vend_idAantalProducten
BRS013
FNG012

  • De WHERE wordt uitgevoerd voordat er gegroepeerd wordt
  • HAVING wordt uitgevoerd na het groeperen
Vraag SQL Stap
Wat wil ik zien? SELECT 5
Waar haal ik het vandaan? FROM 1
Waar wil ik op filteren? WHERE 2
Waar wil ik op groeperen? GROUP BY 3
Welke groepen wil ik filteren? HAVING 4
Waar wil ik op sorteren? ORDER BY 6
  • Het verschil (en overeenkomsten) tussen ORDER BY en GROUP BY:
ORDER BY GROUP BY
Sorteert het resultaat Groepeert rijen
Er kunnen meerdere kolommen toegevoegd worden aan een ORDER BY, ook als ze niet voorkomen in de SELECT Er kunnen meerdere kolommen toegevoegd worden aan een GROUP BY, ook als ze niet voorkomen in de SELECT
Nooit verplicht Verplicht als er kolommen gebruikt worden en aggregaat functies
Werkt op een Alias Werkt niet op een Alias

Bij het gebruik van een GROUP BY kan het lijken dat het resultaat gesorteerd is. Maar dat kan niet met zekerheid gesteld worden.
Gebruik je een GROUP BY plaatst dan ook een ORDER BY. Vertrouw nooit op de GROUP BY instellingen van het DBMS

  1. Geef per land het aantal leveranciers (3)


  1. Geef per product het bestelde aantal aan (7)


  1. Laat de leverancier(s) zien die meer dan 20,- totaal aan producten heeft in de Products tabel (1)


  1. Geef per ordernummer de totaalprijs mits deze boven de 1000 is (3)


  1. Geef per ordernummer, van de eerste 2 orderregels (order_item), de totaalprijs mits deze boven de 1000 is. (2)