SQL GROUP BY — ćwiczenia z odpowiedziami

Zapytanie SELECT zwraca wiersze po jednym. GROUP BY pozwala odpowiedzieć na inne pytania: ile produktów jest w każdej kategorii, jaki przychód daje kategoria albo które kategorie przekraczają zadany próg. Na tej lekcji uczniowie podsumują sprzedaż w szkolnym sklepiku i nauczą się odróżniać filtrowanie wierszy (WHERE) od filtrowania grup (HAVING).

Materiał jest przeznaczony na 45 minut dla uczniów, którzy znają SELECT, WHERE, ORDER BY i aliasy — na przykład po lekcji SQL SELECT. Przykłady działają w MySQL 8 i MariaDB (zapytania sprawdzono na MariaDB 10.4).

Przygotowanie bazy

Pobierz pakiet i w pustej bazie ćwiczeniowej zaimportuj raz plik baza.sql. Tworzy on tabelę sprzedaz z dwunastoma produktami. Plik nie usuwa istniejących tabel; drugi import w tej samej bazie zakończy się błędem. Nie pracuj na bazie produkcyjnej. Po imporcie sprawdź, że SELECT COUNT(*) FROM sprzedaz; zwraca 12.

Kolumna ilosc oznacza liczbę sprzedanych sztuk, a cena — cenę jednej sztuki. Dane są fikcyjne.

Cele i plan lekcji

Po zajęciach uczeń potrafi:

• zapisać zapytanie z GROUP BY i użyć funkcji COUNT, SUM, AVG, MIN i MAX;
• obliczyć wartość z kilku kolumn wewnątrz funkcji agregującej, np. SUM(ilosc * cena);
• użyć HAVING do filtrowania wyników grupowania i wyjaśnić jego różnicę od WHERE;
• przewidzieć liczbę wierszy wyniku zapytania z grupowaniem.

Funkcje agregujące i GROUP BY

Funkcja agregująca zamienia wiele wierszy w jedną wartość. Na całej tabeli:

SELECT COUNT(*) AS liczba_produktow, SUM(ilosc) AS sztuk
FROM sprzedaz;

Wynik to jeden wiersz: 12 produktów i 211 sprzedanych sztuk. GROUP BY dzieli wiersze na grupy według wartości kolumny i liczy funkcję osobno dla każdej grupy:

SELECT kategoria, COUNT(*) AS liczba_produktow, SUM(ilosc) AS sztuk
FROM sprzedaz
GROUP BY kategoria
ORDER BY kategoria;

Wynik ma 3 wiersze — po jednym dla każdej kategorii:

Zasada: w SELECT wolno wypisać kolumny, według których grupujemy, oraz funkcje agregujące. Kolumna spoza GROUP BY bez funkcji — np. produkt — w MySQL 8 domyślnie powoduje błąd. MariaDB w domyślnych ustawieniach zwróci jakiś wiersz z grupy, co jest groźne, bo wynik wygląda poprawnie, choć jest przypadkowy. Warto pokazać to klasie i ustalić regułę: jeśli kolumna nie jest w GROUP BY i nie jest w funkcji, nie ma jej w SELECT.

W funkcji agregującej można obliczać wyrażenia. Przychód kategorii to suma iloczynów ilości i ceny:

SELECT kategoria, SUM(ilosc * cena) AS przychod
FROM sprzedaz
GROUP BY kategoria
ORDER BY przychod DESC;

Wynik: akcesoria 1346,60, elektronika 667,38 i papiernicze 361,00. Alias przychod można użyć w ORDER BY, bo sortowanie wykonuje się po obliczeniu wyniku.

WHERE kontra HAVING

WHERE odrzuca wiersze przed grupowaniem, a HAVING filtruje grupy po grupowaniu i może używać funkcji agregujących. Nie można napisać WHERE SUM(ilosc * cena) > 500 — MariaDB zwróci błąd „Invalid use of group function”.

SELECT kategoria, SUM(ilosc * cena) AS przychod
FROM sprzedaz
GROUP BY kategoria
HAVING SUM(ilosc * cena) > 500
ORDER BY przychod DESC;

Wynik: dwie kategorie, akcesoria i elektronika. Papiernicze (361,00) odpadają, bo grupa nie spełnia warunku. Obie klauzule można połączyć:

SELECT kategoria, COUNT(*) AS liczba
FROM sprzedaz
WHERE cena >= 10
GROUP BY kategoria
HAVING COUNT(*) >= 3
ORDER BY kategoria;

Najpierw WHERE zostawia tylko produkty od 10 zł, potem GROUP BY je grupuje, a HAVING odrzuca kategorie z mniejszą liczbą takich produktów. Wynik: akcesoria 4 i elektronika 3 (papiernicze nie mają żadnego produktu za 10 zł lub więcej, więc w ogóle nie tworzą grupy).

Trzy zadania

Zadanie 1. Podsumowanie kategorii. Dla każdej kategorii wyświetl nazwę, liczbę produktów (liczba_produktow) i łączną liczbę sprzedanych sztuk (sztuk). Posortuj alfabetycznie. Podaj liczbę wierszy wyniku.

Zadanie 2. Przychód. Dla każdej kategorii oblicz przychód jako sumę iloczynów ilości i ceny (kolumna przychod). Posortuj od największego. Który wynik jest drugi i ile wynosi?

Zadanie 3. Filtrowanie grup. Wyświetl kategorie, w których przychód przekracza 500 zł. Następnie wyświetl kategorie, w których są co najmniej 3 produkty w cenie od 10 zł (nazwa i liczba takich produktów). Wyjaśnij, które filtrowanie wymaga WHERE, a które HAVING.

Dla chętnych: dla każdej kategorii wyświetl cenę najdroższego produktu (najdrozszy) i średnią cenę zaokrągloną do 2 miejsc (srednia). Użyj MAX, AVG i ROUND.

Rozwiązania z oczekiwanymi wynikami

Zadanie 1 — zapytanie jest takie jak we wspólnym przykładzie; wynik ma 3 wiersze (patrz tabela wyżej).

Zadanie 2 — zapytanie z SUM(ilosc * cena) jak wyżej. Wynik: akcesoria 1346,60; drugi wiersz to elektronika z przychodem 667,38; na końcu papiernicze 361,00. Obliczenie kontrolne dla akcesoriów: 8·79 + 15·12,50 + 7·24 + 9·39,90 = 632 + 187,50 + 168 + 359,10 = 1346,60.

Zadanie 3 — dwa zapytania z poprzedniej sekcji. Pierwsze zwraca akcesoria i elektronikę (przychód powyżej 500 zł), drugie akcesoria (4) i elektronikę (3). Filtr „przychód powyżej 500” dotyczy wartości obliczonej dla całej grupy, więc wymaga HAVING. Filtr „cena od 10 zł” dotyczy pojedynczych produktów, więc używamy WHERE. Filtr „co najmniej 3 produkty” dotyczy liczby wierszy w grupie — znów HAVING.

Dla chętnych:

SELECT kategoria, MAX(cena) AS najdrozszy, ROUND(AVG(cena), 2) AS srednia
FROM sprzedaz
GROUP BY kategoria
ORDER BY kategoria;

Wynik: akcesoria 79,00 i 38,85; elektronika 49,00 i 30,87; papiernicze 6,40 i 3,00. Średnia cena papierniczych to (3,50 + 1,20 + 0,90 + 6,40) / 4 = 3,00; nie jest ważona ilością — to zwykła średnia z cen produktów.

Typowe błędy

Pytania kontrolne i punktacja

1. Czym różni się WHERE od HAVING? W jakiej kolejności są wykonywane?
2. Dlaczego w zapytaniu z GROUP BY kategoria nie można w SELECT wypisać kolumny produkt bez funkcji?
3. Ile wierszy zwróci SELECT kategoria, COUNT(*) FROM sprzedaz GROUP BY kategoria i dlaczego?

Odpowiedzi: WHERE filtruje wiersze przed grupowaniem i nie może używać funkcji agregujących, a HAVING filtruje gotowe grupy i może ich używać. Kolumna produkt ma w grupie wiele wartości, więc nie wiadomo, którą pokazać. Trzy wiersze, bo w tabeli są trzy kategorie — jeden wiersz na grupę.

Proponowana punktacja (razem 10 punktów): zadanie 1 — GROUP BY z COUNT i SUM oraz aliasami 2 pkt, sortowanie 1 pkt; zadanie 2 — SUM(ilosc * cena) 2 pkt, sortowanie malejące i odczytanie drugiego wyniku 1 pkt; zadanie 3 — HAVING dla przychodu 1 pkt, WHERE plus HAVING dla liczby produktów 1 pkt, wyjaśnienie 1 pkt; pytanie 1 — 1 pkt. Zadanie dla chętnych nie jest wymagane do kompletu punktów.

Baza SQL i karta pracy do pobrania

• Pakiet SQL: baza do importu, polecenia, rozwiązania i karta pracy
• Karta pracy ucznia — otwórz i wydrukuj

Pakiet zawiera baza.sql, start.sql z poleceniami, rozwiazania.sql i instrukcję importu. Materiały są dostępne bez logowania. Klucz jest publiczny i służy nauce oraz samodzielnemu sprawdzeniu pracy, nie poufnemu sprawdzianowi.

Co dalej po GROUP BY?

Kolejny krok to grupowanie po wielu kolumnach i łączenie grupowania ze złączeniami — zobacz lekcję o SQL JOIN. Wyniki zapytań warto też wyświetlić na stronie: temu służy lekcja o PHP i MySQL z PDO. Całość od projektowania tabel po funkcje agregujące obejmuje kurs SQL w TablicaPro.

Źródła: GROUP BY i funkcje agregujące w MySQL 8.4, GROUP BY a ONLY_FULL_GROUP_BY oraz GROUP BY w dokumentacji MariaDB.