SQL Basis

• 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;

• 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;

• 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;

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;

• 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;

• De WHERE wordt uitgevoerd voordat er gegroepeerd wordt
• HAVING wordt uitgevoerd na het groeperen

• Het verschil (en overeenkomsten) tussen ORDER BY en GROUP BY:

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

41. Geef per land het aantal leveranciers (3)

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

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

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

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