GROUP BY#
%LOAD ../data/employees.db
Tot nu toe hebben we uitsluitend query’s gezien waarbij informatie werd verwacht met betrekking tot individuele rijen uit de tabellen. Het komt echter regelmatig voor dat we geïnteresseerd zijn in geaggregeerde informatie. Hiermee wordt bedoeld informatie die niet meer is gebaseerd op afzonderlijke rijen, maar op verzamelingen van rijen. Rijen die op een bepaalde manier bij elkaar horen.
Stel bijvoorbeeld dat we een overzicht willen van het gemiddelde salaris (salary) van elke afdeling (department_id). Bij dit soort query’s hebben we de GROUP BY component van het SELECT commando nodig. Voor het gemak hebben we de gemiddelde salarissen alvast afgerond.
SELECT ROUND(AVG(salary))
FROM employees
GROUP BY department_id;
| ROUND(AVG(salary)) |
|---|
| 7000.0 |
| 4400.0 |
| 9500.0 |
| 3500.0 |
| 6400.0 |
| 10033.0 |
| 19333.0 |
| 10150.0 |
Nu zegt zo’n overzicht met enkel gemiddelde salarissen niet zo veel. Je ziet niet meteen waar die gemiddelde salarissen op slaan. Daarom zetten we er een kolom bij die aangeeft welke afdeling (department_id) bij welk gemiddelde salaris hoort.
SELECT department_id, ROUND(AVG(salary))
FROM employees
GROUP BY department_id;
| department_id | ROUND(AVG(salary)) |
|---|---|
| 7000.0 | |
| 10 | 4400.0 |
| 20 | 9500.0 |
| 50 | 3500.0 |
| 60 | 6400.0 |
| 80 | 10033.0 |
| 90 | 19333.0 |
| 110 | 10150.0 |
De rijen worden eerst gegroepeerd op department_id en daarna wordt de AVG functie (gemiddelde) toegepast op elke groep.
Of stel dat je het maximum salaris wil weten van elke afdeling. Eerst een overzicht zonder de afdelingsid’s.
SELECT MAX(salary)
FROM employees
GROUP BY department_id;
| MAX(salary) |
|---|
| 7000 |
| 4400 |
| 13000 |
| 5800 |
| 9000 |
| 11000 |
| 24000 |
| 12000 |
Om te laten zien welk maximum salaris bij welke afdeling hoort, voeg je het department_id toe op de SELECT regel.
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;
| department_id | MAX(salary) |
|---|---|
| 7000 | |
| 10 | 4400 |
| 20 | 13000 |
| 50 | 5800 |
| 60 | 9000 |
| 80 | 11000 |
| 90 | 24000 |
| 110 | 12000 |
Als je een groepsfunctie gebruikt op de SELECT regel voorafgegaan door een kolomnaam, dan moet die kolomnaam genoemd worden op de GROUP BY regel. Dus als er staat SELECT department_id, MAX (salary) op de SELECT regel, dan moet op de GROUP BY regel staan GROUP BY department_id.
De syntaxis is altijd in deze volgorde:
SELECT kolomnaam, groepsfunctie
FROM tabel
WHERE
GROUP BY kolomnaam
HAVING
ORDER BY
Voorbeeld: Tel het aantal per job_id. Maar IT_PROG mag niet voorkomen in het overzicht en alleen de job_id’s die meer dan één keer voorkomen mogen worden getoond.
SELECT job_id, COUNT(job_id)
FROM employees
WHERE job_id <> 'IT_PROG'
GROUP BY job_id
HAVING COUNT(*) > 1
ORDER BY COUNT(job_id);
| job_id | COUNT(job_id) |
|---|---|
| AD_VP | 2 |
| SA_REP | 3 |
| ST_CLERK | 4 |
Je mag een alias (AS) gebruiken op de GROUP BY of de ORDER BY regel.
SELECT job_id AS functie, COUNT(job_id) AS aantal
FROM employees
WHERE job_id <> 'IT_PROG'
GROUP BY functie
HAVING COUNT(*) > 1
ORDER BY aantal DESC;
| functie | aantal |
|---|---|
| ST_CLERK | 4 |
| SA_REP | 3 |
| AD_VP | 2 |
NULL waarden#
Groepsfuncties negeren NULL waarden. Vergelijk maar eens de volgende twee query’s waarbij belangrijk is om te weten dat er één werknemer is zonder een department_id (Grant). En let dan op het verschil in de bovenste rij.
SELECT department_id, COUNT(department_id)
FROM employees
GROUP BY department_id;
| department_id | COUNT(department_id) |
|---|---|
| 0 | |
| 10 | 1 |
| 20 | 2 |
| 50 | 5 |
| 60 | 3 |
| 80 | 3 |
| 90 | 3 |
| 110 | 2 |
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
| department_id | COUNT(*) |
|---|---|
| 1 | |
| 10 | 1 |
| 20 | 2 |
| 50 | 5 |
| 60 | 3 |
| 80 | 3 |
| 90 | 3 |
| 110 | 2 |
WHERE#
Het is mogelijk een WHERE te gebruiken in combinatie met GROUP BY.
Stel dat je een overzicht wil maken met de hoogste salarissen per afdeling. Dat kan met de volgende query.
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;
| department_id | MAX(salary) |
|---|---|
| 7000 | |
| 10 | 4400 |
| 20 | 13000 |
| 50 | 5800 |
| 60 | 9000 |
| 80 | 11000 |
| 90 | 24000 |
| 110 | 12000 |
In de WHERE kun je voorwaarden stellen. Stel bijvoorbeeld dat het salaris van de hoogste baas niet mee mag tellen voor het overzicht. King werkt op afdeling 90. Van die afdeling zal nu het hoogste salaris worden aangepast.
SELECT department_id, MAX(salary)
FROM employees
WHERE last_name != 'King'
GROUP BY department_id;
| department_id | MAX(salary) |
|---|---|
| 7000 | |
| 10 | 4400 |
| 20 | 13000 |
| 50 | 5800 |
| 60 | 9000 |
| 80 | 11000 |
| 90 | 17000 |
| 110 | 12000 |
ORDER BY#
Sorteren bij groepsfuncties kan op verschillende manieren. Het kan met het noemen van de groepsfunctie die op de eerste regel staat (MAX(salary)), met behulp van de alias van de kolom (maximum_salaris) of door het nummer van de volgorde van de kolom te noemen, in dit geval 2.
SELECT department_id AS afdeling, MAX(salary)AS maximum_salaris
FROM employees
GROUP BY department_id
ORDER BY MAX(salary);
| afdeling | maximum_salaris |
|---|---|
| 10 | 4400 |
| 50 | 5800 |
| 7000 | |
| 60 | 9000 |
| 80 | 11000 |
| 110 | 12000 |
| 20 | 13000 |
| 90 | 24000 |
SELECT department_id AS afdeling, MAX(salary) AS maximum_salaris
FROM employees
GROUP BY department_id
ORDER BY maximum_salaris;
| afdeling | maximum_salaris |
|---|---|
| 10 | 4400 |
| 50 | 5800 |
| 7000 | |
| 60 | 9000 |
| 80 | 11000 |
| 110 | 12000 |
| 20 | 13000 |
| 90 | 24000 |
SELECT department_id AS afdeling, MAX(salary) AS maximum_salaris
FROM employees
GROUP BY department_id
ORDER BY 2;
| afdeling | maximum_salaris |
|---|---|
| 10 | 4400 |
| 50 | 5800 |
| 7000 | |
| 60 | 9000 |
| 80 | 11000 |
| 110 | 12000 |
| 20 | 13000 |
| 90 | 24000 |
HAVING#
Stel dat je het maximum salaris wil zien van elke afdeling, maar enkel van de afdelingen met meer dan één werknemer. Dat kun je niet bereiken met een WHERE maar je moet daar een HAVING voor gebruiken.
Eerst maken we de query zonder HAVING.
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;
| department_id | MAX(salary) |
|---|---|
| 7000 | |
| 10 | 4400 |
| 20 | 13000 |
| 50 | 5800 |
| 60 | 9000 |
| 80 | 11000 |
| 90 | 24000 |
| 110 | 12000 |
En dan de query met HAVING zodat je het verschil ziet.
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;
| department_id | MAX(salary) |
|---|---|
| 20 | 13000 |
| 50 | 5800 |
| 60 | 9000 |
| 80 | 11000 |
| 90 | 24000 |
| 110 | 12000 |
We maken een alias ‘afdeling’ voor department_id en een alias ‘max_salaris’ voor MAX(salary). We sorteren op het maximum salaris (kolom 2 op de bovenste regel) van hoog naar laag (DESC).
SELECT department_id AS afdeling, MAX(salary) AS max_salaris
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1
ORDER BY 2 DESC;
| afdeling | max_salaris |
|---|---|
| 90 | 24000 |
| 20 | 13000 |
| 110 | 12000 |
| 80 | 11000 |
| 60 | 9000 |
| 50 | 5800 |
En we voegen nog een WHERE toe om de query compleet te maken. De afdeling met als department_id 90 mag niet meetellen. Op de regel met ORDER BY mag je ook het alias (max_salaris) gebruiken dat op de SELECT regel staat.
SELECT department_id AS afdeling, MAX(salary) AS max_salaris
FROM employees
WHERE department_id != 90
GROUP BY afdeling
HAVING COUNT(*) > 1
ORDER BY 2 DESC;
| afdeling | max_salaris |
|---|---|
| 20 | 13000 |
| 110 | 12000 |
| 80 | 11000 |
| 60 | 9000 |
| 50 | 5800 |
Voor het ongelijk teken != mag je ook <> gebruiken.