Funkcje agregujące i grupowanie danych (GROUP BY)

Po co się tego uczymy?

Panel administracyjny sklepu, który pokazuje "liczbę zamówień dzisiaj", "średnią wartość koszyka" albo "najlepiej sprzedający się produkt" — wszystkie te liczby powstają dzięki funkcjom agregującym. Bez nich musiałbyś pobrać tysiące wierszy i liczyć to ręcznie w aplikacji.

Teoria

Czasem nie interesuje Cię każdy wiersz z osobna, tylko PODSUMOWANIE całego zbioru — ile mamy klientów, jaka jest średnia cena, który produkt jest najdroższy. Do takich obliczeń służą funkcje agregujące.

Funkcja / Działanie:
COUNT() — liczy wiersze.
SUM() — liczy sumę.
AVG() — liczy średnią.
MAX() — zwraca największą wartość.
MIN() — zwraca najmniejszą wartość.

Przykłady:

SELECT COUNT(*) AS liczba_klientow FROM Klienci;
SELECT AVG(cena) AS srednia_cena FROM Produkty;
SELECT MAX(cena) AS najdrozszy FROM Produkty;

Funkcje agregujące same w sobie liczą jedną wartość dla CAŁEJ tabeli. Żeby policzyć np. sumę osobno DLA KAŻDEJ kategorii produktów, potrzebujesz GROUP BY:

SELECT kategoria, COUNT(*) AS ile, AVG(cena) AS srednia
FROM Produkty
GROUP BY kategoria;

To zapytanie podzieli produkty na grupy według kategorii, a potem policzy COUNT i AVG OSOBNO dla każdej grupy.

Jeśli chcesz jeszcze przefiltrować WYNIKI grupowania (np. pokazać tylko kategorie z więcej niż 5 produktami), używasz HAVING — to jest "WHERE dla grup", bo zwykłego WHERE nie można użyć z funkcją agregującą:

SELECT kategoria, COUNT(*) AS ile
FROM Produkty
GROUP BY kategoria
HAVING COUNT(*) > 5;

Przykład z życia

Wyobraź sobie listę wszystkich zamówień w sklepie muzycznym — setki wierszy. Zamiast przeglądać je jeden po drugim, pytasz bazę wprost: "ile mamy klientów", "jaka jest średnia wartość zamówienia", "który produkt jest najdroższy" — i dostajesz gotową liczbę.

Kod (SQL)

-- ile produktów jest w każdej kategorii i jaka jest ich średnia cena

SELECT kategoria, COUNT(*) AS liczba_produktow, AVG(cena) AS srednia_cena
FROM Produkty
GROUP BY kategoria;

-- tylko kategorie, w których jest więcej niż 2 produkty

SELECT kategoria, COUNT(*) AS liczba_produktow
FROM Produkty
GROUP BY kategoria
HAVING COUNT(*) > 2;

-- łączna wartość zamówień każdego klienta (agregacja po JOIN-ie)

SELECT Klienci.imie, Klienci.nazwisko, SUM(Zamowienia.ilosc * Produkty.cena) AS suma_wydana
FROM Zamowienia
JOIN Klienci ON Zamowienia.id_klienta = Klienci.id_klienta
JOIN Produkty ON Zamowienia.id_produktu = Produkty.id_produktu
GROUP BY Klienci.id_klienta;

Komentarz i wyjaśnienie kodu

Pierwsze zapytanie grupuje produkty po kolumnie kategoria i dla każdej grupy liczy dwie rzeczy naraz: COUNT (ile produktów) i AVG (jaka średnia cena). Drugie zapytanie robi to samo, ale dokłada HAVING, żeby odfiltrować grupy z małą liczbą produktów — to different od WHERE, bo HAVING działa PO grupowaniu, na już policzonych wynikach. Trzecie zapytanie łączy JOIN z GROUP BY: najpierw łączymy trzy tabele, a dopiero potem grupujemy po kliencie i sumujemy wartość jego zamówień.

Ćwiczenie samodzielne

Napisz zapytanie, które pokaże, ile zamówień złożył każdy klient (GROUP BY po kliencie, COUNT zamówień).

Zadania do pracy własnej

Zadanie 1. GROUP BY w sklepie muzycznym

Uwaga przed importem: plik z bazą jest spakowany jako .gz. phpMyAdmin w XAMPP przyjmuje takie pliki bez rozpakowywania. Jeżeli po pobraniu nazwa wygląda np. sklep_muzyczny.sql_.gz i import nie przejdzie, zmień nazwę pliku na sklep_muzyczny.sql.gz, a potem wybierz go w zakładce Importuj.

Baza do pracy: Sklep muzyczny (.sql.gz). Zaimportuj spakowany plik SQL w XAMPP/phpMyAdmin, a następnie wykonaj zapytania.
1. Policz liczbę wszystkich klientów.
2. Policz ilu klientów pochodzi z każdego miasta.
3. Oblicz średnią cenę produktów w każdej kategorii.
4. Oblicz łączną wartość wszystkich zamówień.
5. Oblicz sumę zamówień dla każdego klienta.
6. Policz liczbę zamówień dla każdego klienta.
7. Pokaż kategorie, których średnia cena przekracza 200 zł.
8. Pokaż trzy produkty o największej łącznej liczbie zamówionych sztuk.
9. Oblicz wartość sprzedaży dla każdego dnia.
10. Pokaż klientów, których suma zamówień przekracza 1000 zł.
Zadanie 2. GROUP BY w bibliotece szkolnej

Uwaga przed importem: plik z bazą jest spakowany jako .gz. phpMyAdmin w XAMPP przyjmuje takie pliki bez rozpakowywania. Jeżeli po pobraniu nazwa wygląda np. sklep_muzyczny.sql_.gz i import nie przejdzie, zmień nazwę pliku na sklep_muzyczny.sql.gz, a potem wybierz go w zakładce Importuj.

Baza do pracy: Biblioteka szkolna (.sql.gz). Zaimportuj spakowany plik SQL w XAMPP/phpMyAdmin, a następnie wykonaj zapytania.
1. Policz wszystkie książki w tabeli.
2. Policz liczbę książek w każdym gatunku.
3. Oblicz średnią liczbę stron według autorów.
4. Wyświetl najstarszą i najnowszą książkę w każdym gatunku.
5. Policz liczbę książek wydanych w każdym roku.
6. Wyświetl autorów, którzy mają więcej niż jedną książkę.
7. Oblicz średnią liczbę stron dla każdego gatunku i posortuj malejąco.
8. Wyświetl gatunki, w których średnia liczba stron przekracza 350.
9. Znajdź najdłuższą książkę każdego autora.
10. Policz książki wydane przed 1900 i od 1900 roku, używając CASE.
Zadanie 3. GROUP BY w aptece i magazynie

Uwaga przed importem: plik z bazą jest spakowany jako .gz. phpMyAdmin w XAMPP przyjmuje takie pliki bez rozpakowywania. Jeżeli po pobraniu nazwa wygląda np. sklep_muzyczny.sql_.gz i import nie przejdzie, zmień nazwę pliku na sklep_muzyczny.sql.gz, a potem wybierz go w zakładce Importuj.

Baza do pracy: Apteka Zdrowie (.sql.gz). Zaimportuj spakowany plik SQL w XAMPP/phpMyAdmin, a następnie wykonaj zapytania.
1. Wyświetl wszystkie leki wraz z kategorią i producentem.
2. Wyświetl leki, których stan magazynowy jest mniejszy niż 50.
3. Policz liczbę leków w każdej kategorii.
4. Oblicz średnią cenę leków według kategorii.
5. Oblicz wartość magazynu każdego leku: cena * ilość_magazyn.
6. Pokaż trzy najdroższe leki.
7. Wyświetl sprzedaż: nazwa leku, ilość, data, wartość sprzedaży.
8. Oblicz sumę sprzedanych sztuk dla każdego leku.
9. Pokaż producentów, których leki były sprzedawane.
10. Pokaż kategorie, w których średnia cena przekracza 20 zł.

Typowe błędy

Najczęstszy błąd to umieszczenie w SELECT kolumny, po której NIE grupujemy i która nie jest objęta funkcją agregującą — SQL zgłosi błąd albo (w niektórych bazach) zwróci przypadkową wartość. Drugi błąd: próba użycia funkcji agregującej w WHERE zamiast w HAVING (WHERE działa przed grupowaniem i nie "widzi" jeszcze wyników agregacji). Trzeci: mylenie COUNT(*) (liczy wszystkie wiersze) z COUNT(kolumna) (liczy tylko wiersze, gdzie ta kolumna nie jest NULL).

Nawiązanie do egzaminu zawodowego

Funkcje agregujące i grupowanie to jeden z typowych elementów efektu "wyszukuje informacje w bazie danych przy użyciu języka SQL" — zadania praktyczne w INF.03 często wymagają policzenia sum, średnich albo zestawień pogrupowanych po kategorii czy dacie.