średni SQLGROUP BYCOUNTmatura rozszerzona z informatyki

GROUP BY – grupowanie i agregacja

Q: Czy można grupować po dwóch kolumnach?

Tak – GROUP BY klasa, przedmiot utworzy osobną grupę dla każdej pary wartości. Grup będzie wtedy tyle, ile faktycznie występujących kombinacji, a nie iloczyn liczby klas i przedmiotów.

Q: Dlaczego baza odmawia wyświetlenia kolumny, o którą proszę?

Bo nie ma jej w GROUP BY ani nie jest objęta funkcją agregującą. Dla grupy złożonej z wielu wierszy taka kolumna nie ma jednej wartości, więc baza nie zgaduje – zgłasza błąd. Dopisz kolumnę do GROUP BY albo obejmij ją funkcją typu MAX().

Grupowanie wierszy po wspólnej wartości i liczenie wartości zbiorczych funkcjami COUNT, SUM, AVG, MIN i MAX oraz filtrowanie grup klauzulą HAVING.

Opis

Dotychczasowe zapytania zwracały wiersze pojedynczo: jeden uczeń – jeden wiersz wyniku. Często jednak pytanie dotyczy nie pojedynczego wiersza, tylko całej grupy: ilu uczniów jest w każdej klasie, jaka jest średnia ocen z każdego przedmiotu, kto ma najwyższy wynik w roczniku. Do tego służy GROUP BY.

Na czym polega grupowanie. GROUP BY klasa mówi bazie: „zbierz razem wszystkie wiersze mające tę samą wartość w kolumnie klasa". Z pięciu wierszy o trzech różnych klasach powstają trzy grupy. Od tej chwili baza przestaje myśleć o pojedynczych uczniach, a zaczyna o grupach – i wynik ma dokładnie tyle wierszy, ile jest grup.

Funkcje agregujące to narzędzia, którymi z każdej grupy wyciąga się jedną liczbę. COUNT(*) zlicza wiersze w grupie, SUM() sumuje wartości, AVG() liczy średnią, a MIN() i MAX() znajdują wartość najmniejszą i największą. Każda z nich zamienia całą grupę w pojedynczą wartość.

Reguła, która rozstrzyga większość błędów. W SELECT mogą znaleźć się tylko dwie rzeczy: kolumny wymienione w GROUP BY oraz funkcje agregujące. Nie da się poprosić o nazwisko obok AVG(srednia) przy grupowaniu po klasie – bo w jednej klasie jest wiele nazwisk i baza nie wie, które miałaby pokazać. Jeśli chcesz zobaczyć kolumnę w wyniku, musi być albo tym, po czym grupujesz, albo poddana agregacji.

WHERE kontra HAVING. To rozróżnienie wraca na maturze regularnie. WHERE działa przed grupowaniem – odsiewa pojedyncze wiersze, zanim powstaną grupy. HAVING działa po grupowaniu – odsiewa całe grupy na podstawie policzonej dla nich wartości. „Tylko oceny z informatyki" to zadanie dla WHERE. „Tylko klasy liczące więcej niż dwóch uczniów" to zadanie dla HAVING, bo liczbę uczniów można poznać dopiero po utworzeniu grup.

Grupować można także wynik złączenia tabel – wtedy GROUP BY pracuje już na sklejonych wierszach.

Zapytanie

-- Trzy klasy w tabeli dają trzy wiersze wyniku
SELECT klasa, COUNT(*), AVG(srednia)
FROM uczniowie
GROUP BY klasa
ORDER BY klasa

Wynik zapytania

klasa	COUNT(*)	AVG(srednia)
3A	2	4.93
3B	2	3.65
3C	1	3.90

-- HAVING odsiewa całe grupy, WHERE odsiałby pojedyncze wiersze
SELECT klasa, COUNT(*)
FROM uczniowie
GROUP BY klasa
HAVING COUNT(*) > 1

Wynik zapytania

klasa	COUNT(*)
3A	2
3B	2

-- WHERE przed grupowaniem, HAVING po nim - w jednym zapytaniu.
-- WHERE odsiewa Nowaka (3.20), więc w klasie 3B zostaje sam Dąbrowski.
SELECT klasa, AVG(srednia)
FROM uczniowie
WHERE srednia >= 3.50
GROUP BY klasa
HAVING AVG(srednia) > 4.00

Wynik zapytania

klasa	AVG(srednia)
3A	4.93
3B	4.10

Wizualizacja

Tabela wejściowa

nazwisko	klasa	srednia
Kowalska	3A	4.75
Nowak	3B	3.2
Wiśniewski	3A	5.1
Zając	3C	3.9
Dąbrowski	3B	4.1

→

Wynik

klasa	COUNT(*)	AVG(srednia)
3A	2	4.93
3B	2	3.65
3C	1	3.90

Na maturze

Policz, ile wierszy powinien mieć wynik, zanim uruchomisz zapytanie. Przy grupowaniu po klasie wierszy będzie tyle, ile jest różnych klas – nie tylu, ilu jest uczniów. Jeśli wynik ma długość oryginalnej tabeli, to znaczy, że grupowanie nie zadziałało tak, jak zakładałeś.

Drugi klasyk to COUNT(*) kontra COUNT(kolumna). Pierwszy zlicza wszystkie wiersze w grupie. Drugi pomija te, w których wskazana kolumna jest pusta. Przy kompletnych danych dają ten sam wynik i różnica bywa niewidoczna – aż do zadania, w którego danych celowo są puste pola.

Trzecia rzecz: jeśli polecenie brzmi „dla każdego / w każdej / z podziałem na", to prawie zawsze sygnał, że potrzebne jest GROUP BY. Jeżeli dodatkowo pada warunek na policzonej wartości („klasy, w których średnia przekracza…"), to znak, że obok będzie HAVING.

Najczęściej zadawane pytania

Czy GROUP BY sortuje wynik?

Nie należy na to liczyć. Wynik grupowania bywa uporządkowany przypadkiem, ale gwarancji nie ma – jeśli kolejność ma znaczenie, dopisz ORDER BY.

Czy można grupować po dwóch kolumnach?

Tak – GROUP BY klasa, przedmiot utworzy osobną grupę dla każdej pary wartości. Grup będzie wtedy tyle, ile faktycznie występujących kombinacji, a nie iloczyn liczby klas i przedmiotów.

Dlaczego baza odmawia wyświetlenia kolumny, o którą proszę?

Bo nie ma jej w GROUP BY ani nie jest objęta funkcją agregującą. Dla grupy złożonej z wielu wierszy taka kolumna nie ma jednej wartości, więc baza nie zgaduje – zgłasza błąd. Dopisz kolumnę do GROUP BY albo obejmij ją funkcją typu MAX().

↗

Powiązane zagadnienia

Zagadnienia z tego samego obszaru matury – warto je powtórzyć razem:

Funkcje agregująceSQL – agregacja i grupowanie AND, OR, NOT – łączenie warunkówSQL – zapytania AS, wyrażenia i funkcja IIfSQL – funkcje i wyrażenia BETWEEN i IN – skróty warunkówSQL – zapytania DISTINCT – usuwanie duplikatówSQL – zapytania Funkcje datySQL – funkcje i wyrażenia