Tabela: pracownicy
pracownik_id
imie
nazwisko
stanowisko_id
wydzial_id
pensja
prowizja
Grupowanie wierszy
Do tego momentu funkcje agregujące wykonywały swoje operacje traktując dane jako niezróżnicowane i jednolite typy informacji. Na przykład obliczanie średniej prowizji dla wszystkich pracowników dotyczyło czytania wszystkich wierszy w kolumnie prowizja. Jednak możemy przyjąć inny scenariusz,w którym potrzebne jest obliczanie średniej prowizji dla pracowników każdego wydziału.
Klauzula Group by
Używając klauzuli GROUP BY możemy podzielić wiersze w tabeli ma małe grupy. I wtedy użyć funkcji agregujących do uzyskana wyników dla poszczególnych grup.
Zadanie 6: rozwiązanie
Oblicz średnią prowizję dla pracowników w poszczególnych wydziałach.
SELECT wydzial_id, AVG (pensja)
FROM pracownicy
GROUP BY wydzial_id;
Ponieważ nie ma klauzuli WHERE zwracane są wszystkie wiersze z tabeli pracownicy. Następnie wiersze są grupowane według wydzial_id, więc funkcja AVG jest stosowana do kolumny pensja obliczając średnią pensję dla każdego wydziału.
Uwaga. Kiedy używasz GROUP BY upewnij się iż wszystkie kolumny, które nie mają funkcji agregujących a są wymienione w SELECT, są zawarte w klauzuli GROUP BY.
Czasami możesz potrzebować wyświetlić wyniki dla grup bez grup.
Zadanie 7
Wyświetl całkowitą sumę wynagrodzeń, które są wypłacane poszczególnym stanowisko w poszczególnych wydziałach
SELECT wydzial_id, stanowisko_id, sum(pensja)
FROM pracownicy
GROUP BY wydzial_id, job_id;
Najpierw tabela pracowników podzielona jest ze względu na id_wydziału, a następnie każda z tych grup jest pogrupowana według stanowisk.
Zauważ, że GROUP BY grupuje wiersze lecz nie gwarantuje uporządkowania wyników. Aby posortować grupowania użyj klauzuli ORDER BY.
SELECT wydzial_id, stanowisko_id, sum(pensja)
FROM pracownicy
GROUP BY wydzial_id, job_id
ORDER BY job_id;
Klauzula Group By:
- wszystkie kolumny w klauzuli SELECT, które nie są użyte w funkcjach agregujących muszą być użyte w klauzuli GROUP BY
- używając klauzuli WHERE można wyłączyć wiersze zanim przydzieli się je do grup. Nie można zatem użyć Where by ograniczyć grupy np:SELECT wydzial_id, AVG(pensja)
FROM pracownicy
WHERE AVG(pensja)>4000
GROUP BY wydzial_id; - nie można używać aliasu kolumny w klauzuli GROUP BY np:
SELECT wydział_id as wydz, AVG(pensja)
FROM pracownicy
GROUP BY wydz;
Poodbnie jak klauzuli WHERE, można użyć klauzuli having aby ograniczać grypy.
Klauzula HAVING
Zadanie 8
Wyświetl maksymalne pencsje w wydziłach, które są wyższe niż 8000.
Jak to zrobić? Należy znależć maksymalną prowizję dla każdego wydziału poprzez grupowanie według numeru wydziału. W tej systuacji nie możemy jednak zastosowac klauzuli Where (patrz punkty wyżej).
SELECT wydzial_id , MAX(pensja)
FROM pracownicy
GROUP BY wydział_id
HAVING MAX(pensja)>8000;
Jak wykonywane jest to zapytanie? Gdy używasz HAVING Serwer Oracle wykonuje następujące kroki:
- Najpierw wiersze są grupowane zgodnie z GROUP BY
- Następnie do grup zastowawna jest funkcja agregująca (MAX)
- Grupy, które spełniają krytera HAVING są wyświetlane
Zadanie 9
Wyświetl raport obliczający średnią pensję w każdym wydziale i wtedy wyświetlający maksymalną średnią pensję.
SELECT MAX(AVG(pensja))
FROM pracownicy
GROUP BY wydzial_id;
Zauważ, że klauzula GROUP BY jest obowiązkowa podczas zagnieżdżania funkcji grupujących.