GROUP BYA MySQL-ben a GROUP BY utasítással csoportokba rendezhetjük a táblázat sorait egy vagy több oszlop értékei alapján.
Segítségével például meg tudjuk számolni, hogy:
A GROUP BY utasítást gyakran használjuk összesítő (aggregáló) függvényekkel, például:
COUNT() – megszámolja a rekordokat.SUM() – összeadja az értékeket.AVG() – kiszámítja az átlagot.MIN() – megkeresi a legkisebb értéket.MAX() – megkeresi a legnagyobb értéket.A**GROUP BY**lényege: az azonos értékű sorokat egy csoportba rendezi, majd az összesítő függvényekkel csoportonként végezhetünk számításokat.
GROUP BY alapvető szintaxisaSELECT oszlop_neve, összesítő_függvény(oszlop_neve)
FROM tabla_neve
GROUP BY oszlop_neve;
A lekérdezés részei:
SELECT: megadja, milyen adatokat szeretnénk megjeleníteni.FROM: megadja, melyik táblából dolgozunk.GROUP BY: meghatározza, melyik oszlop alapján történjen a csoportosítás.GROUP BY használatáraA példában a következő diakok táblát használjuk.
| id | nev | osztaly | eletkor |
|---|---|---|---|
| 1 | Anna | 10.A | 16 |
| 2 | Béla | 10.B | 16 |
| 3 | Csilla | 10.A | 15 |
| 4 | Dávid | 10.B | 17 |
| 5 | Erika | 10.A | 16 |
| 6 | Ferenc | 10.C | 17 |
| 7 | Gábor | 10.B | 16 |
| 8 | Hanna | 10.C | 15 |
Feladat: Határozzuk meg, hogy hány tanuló jár az egyes osztályokba!
SELECT osztaly, COUNT(*) AS letszam FROM diakok GROUP BY osztaly;
Eredmény:
| osztaly | letszam |
|---|---|
| 10.A | 3 |
| 10.B | 3 |
| 10.C | 2 |
Magyarázat:
GROUP BY osztaly azonos osztályba járó tanulókat csoportosítja.COUNT(*) megszámolja az egyes csoportok sorait.AS letszam segítségével nevet adunk a kiszámított oszlopnak.Az eredményben minden osztály csak egyszer szerepel, mellette pedig a hozzá tartozó tanulók száma látható.
A GROUP BY utasításban egyszerre több oszlopot is megadhatunk.
Például csoportosítsuk a tanulókat osztály és életkor szerint!
SELECT osztaly, eletkor, COUNT(*) AS letszam FROM diakok GROUP BY osztaly, eletkor;
Eredmény:
| osztaly | eletkor | letszam |
|---|---|---|
| 10.A | 15 | 1 |
| 10.A | 16 | 2 |
| 10.B | 16 | 2 |
| 10.B | 17 | 1 |
| 10.C | 15 | 1 |
| 10.C | 17 | 1 |
Hogyan működik?
A MySQL azokat a sorokat teszi egy csoportba, amelyekben mindkét megadott oszlop értéke megegyezik. Tehát a két oszlop értékének együttes egyezése határozza meg a csoportokat.
GROUP BY és a WHERE kapcsolataA WHERE segítségével a csoportosítás előtt szűrhetjük meg a rekordokat.
Feladat: Számoljuk meg, hogy hány tanuló jár az egyes osztályokba, de csak a 16 éves vagy annál idősebb tanulókat vegyük figyelembe!
SELECT osztaly, COUNT(*) AS letszam
FROM diakok
WHERE eletkor >= 16
GROUP BY osztaly;
Eredmény:
| osztaly | letszam |
|---|---|
| 10.A | 2 |
| 10.B | 3 |
| 10.C | 1 |
A műveletek sorrendje:
WHERE eletkor >= 16 kiválasztja a megfelelő tanulókat.GROUP BY osztaly osztályok szerint csoportosítja őket.COUNT(*) megszámolja a tanulókat az egyes csoportokban.A WHERE tehát a csoportosítás előtti adatszűrésre szolgál.
GROUP BY és a HAVING kapcsolataA HAVING utasítással a csoportosítás után szűrhetjük meg a csoportokat.
Például szeretnénk megjeleníteni azokat az osztályokat, amelyekbe legalább három tanuló jár.
SELECT osztaly, COUNT(*) AS letszam
FROM diakok
GROUP BY osztaly
HAVING COUNT(*) >= 3;
Eredmény:
| osztaly | letszam |
|---|---|
| 10.A | 3 |
| 10.B | 3 |
A 10.C osztály nem jelenik meg, mert oda csak két tanuló jár.
WHERE és a HAVING közötti különbségWHERE |
HAVING |
|---|---|
| A csoportosítás előtt szűr. | A csoportosítás után szűr. |
| Egyedi rekordok kiválasztására használjuk. | Csoportok kiválasztására használjuk. |
Például: WHERE eletkor >= 16 |
Például: HAVING COUNT(*) >= 3 |
A két utasítás együtt is használható:
SELECT osztaly, COUNT(*) AS letszam
FROM diakok
WHERE eletkor >= 16
GROUP BY osztaly
HAVING COUNT(*) >= 2;
Ez a lekérdezés először kiválasztja a legalább 16 éves tanulókat, majd osztályonként csoportosítja őket, és végül csak azokat az osztályokat jeleníti meg, ahol legalább két ilyen tanuló van.
Fontos: a WHERE és a HAVING nem helyettesíti egymást. Mindig azt használjuk, amelyik az adott szűrési feltételnek megfelel.
GROUP BY és az ORDER BY kapcsolataAz ORDER BY segítségével sorba rendezhetjük a csoportosított lekérdezés eredményét.
Feladat: Jelenítsük meg az osztályok létszámát úgy, hogy a legnagyobb létszámú osztály kerüljön az első helyre!
SELECT osztaly, COUNT(*) AS letszam
FROM diakok
GROUP BY osztaly
ORDER BY letszam DESC;
A két utasítás feladata eltérő:
GROUP BY létrehozza a csoportokat.ORDER BY sorba rendezi az eredményül kapott sorokat.Az alábbi feladatokat a fentebb megadott diakok táblán oldjuk meg! A táblát létrehozó és az adatokat feltöltő utasítások a diakok.sql-ben találhatók!
1. feladat – Osztálylétszám
Készíts lekérdezést, amely megmutatja, hogy hány tanuló jár az egyes osztályokba!
2. feladat – Átlagéletkor
Jelenítsd meg az egyes osztályok átlagéletkorát két tizedesjegyre kerekítve!
3. feladat – Legidősebb tanulók
Határozd meg, hogy az egyes osztályokban hány éves a legidősebb tanuló!
4. feladat – Életkor szerinti csoportosítás
Számold meg, hogy hány 15, 16 és 17 éves tanuló van a táblában!
5. feladat
Számold meg osztályonként a legalább 16 éves tanulókat!
6. feladat
Jelenítsd meg azoknak az osztályoknak a nevét és létszámát, amelyekben legalább három tanuló van!
7. feladat
Készíts lekérdezést, amely osztályonként megjeleníti a tanulók számát, a legnagyobb létszámtól a legkisebb felé haladva!
8. feladat
Számold meg, hogy az egyes osztályokban hány 15, 16 és 17 éves tanuló van! Az eredményben jelenítsd meg az osztályt, életkort és a létszámot!