Dziś powtórzymy zasadę działania klauzuli GROUP BY.
Skorzystamy z bezpłatnej bazy Oracle HR dostępnej tutaj:
http://www.oracle.com/technetwork/database/express-edition/downloads/index.html
Jeżeli wpiszemy proste wyrażenie pokazane poniżej, otrzymamy informację o błędzie. Funkcja AVG oblicza średnie wynagrodzenie z tabeli EMPLOYEES.
SELECT
DEPARTMENT_ID,
AVG(SALARY)
FROM EMPLOYEES;
Niezbędne jest dopisanie klauzuli GRUPED BY, która dedykowana jest do wszystkich funkcji, które podają jeden wspólny wynik dla wskazanej kolumny.
Klauzula GRUPED BY
Schemat działania klauzuli GRUPED BY jest następujący: najpierw po SELECT pojawia się jakaś funkcja wynikowa w połączeniu z nazwami grup. Taki zestaw danych nie może być wyświetlony ponieważ wynik jest jeden a grup jest kilka. Na końcu konieczna jest więc klauzula GRUPED BY.
Prawidłowa składnia powinna wyglądać tak:
Prawidłowe zapytanie ma formę:
SELECT
DEPARTMENT_ID,
AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
Otrzymujemy tabelę wynikową:
Klauzula HAVING
Na końcu tego wpisu zagnieździmy podzapytanie zawierające klauzulę HAVING. Tym razem będzie prosty warunek z HAVING.
Klauzula HAVING w połączeniu z GROUP BY operuje na całych grupach nie na pojedynczych rekordach
SELECT
DEPARTMENT_ID,
AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY) < 6501
Klauzula HAVING uszczegóławia zapytanie. Najpierw funkcja AVG oblicza średnią. Dzięki klauzuli GROUP BY wyniki średnie zostają pogrupowane. Następnie klauzula HAVING wyświetla tylko to, co jest zgodne z zadanym warunkiem.
Klauzula HAVING nie może istnieć bez klauzuli GROUP BY
Funkcje agregujące (nazwane tutaj funkcjami jednego wyniku) umieszczane są po SELECT i po HAVING
W naszym przypadku jest to średnia pensja niższa od 6 500.
Teraz wyświetlimy wszystkie działy, w których jest zatrudnionych więcej niż 15 pracowników lub mniej niż 3.
SELECT
JOB_ID,
COUNT(LAST_NAME)
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING COUNT(LAST_NAME) > 15 OR COUNT(LAST_NAME)<5;
Po klauzuli HAVING musi znajdować się to samo wyrażenie, co po instrukcji SELECT
Otrzymujemy tabelę wynikową:
Kolejny przykład: raport na temat prowizji.
SELECT
JOB_ID,
AVG(COMMISSION_PCT)
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING AVG(COMMISSION_PCT) < 0.3
Klauzula HAVING ma znajdować się po GROUP BY i przed klauzulą OREDER BY
Chcemy dowiedzieć się, w którym dziale średnia pensja jest półtora raza większa od średniej pensji wszystkich działów. Tym razem jest to zagnieżdżenie zapytań z klauzula HAVING.
SELECT
DEPARTMENT_ID,
AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING AVG(SALARY)>
(SELECT
1.5*(AVG(SALARY))
FROM EMPLOYEES);
Otrzymujemy tabelę wynikową:
Schemat użycia klauzuli GROUP BY z HAVING
Klauzula HAVING w połączeniu z GROUP BY wskazuje nam grupy spełniające zadany warunek
Funkcje agregujące (nazwane tutaj funkcjami jednego wyniku) umieszczane są po SELECT i po HAVING.
Użycie HAVING I WHERE w jednym zapytaniu
Chcemy dowiedzieć się ile wynosiła suma wynagrodzeń dla poszczególnych typów stanowisk. Założyliśmy warunek, że nie bierzemy do sumy pojedynczych wynagrodzeń większych od 8 000 oraz, że nie interesują nas sumy wynagrodzeń dla poszczególnych stanowisk, które przekroczą 20 000.
SELECT
JOB_ID,
SUM(SALARY) AS Suma_wynagrodzeń
FROM EMPLOYEES
WHERE SALARY < 8000
GROUP BY JOB_ID
HAVING SUM(SALARY) < 20000;
Otrzymujemy tabelę wynikową:
Tym razem zrobimy raport wielkości wynagrodzeń i średnich wynagrodzeń dla poszczególnych działów.
SELECT
DEPARTMENTS.DEPARTMENT_NAME,
SUM(EMPLOYEES.SALARY) AS SUMA_WYNAGRODZEŃ,
ROUND(AVG(EMPLOYEES.SALARY)) AS ŚREDNIE_WYNAGRODZENIE
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
GROUP BY DEPARTMENTS.DEPARTMENT_NAME
ORDER BY SUMA_WYNAGRODZEŃ DESC;
Otrzymujemy tabelę wynikową:
Teraz zrobimy raport wynagrodzeń dla kategorii stanowisk. Raport będzie uwzględniał średnie wynagrodzenie, maksymalne i minimalne wynagrodzenie oraz ilość stanowisk pracy w danej kategorii stanowisk.
SELECT
EMPLOYEES.JOB_ID,
MIN(EMPLOYEES.SALARY),
MAX(EMPLOYEES.SALARY),
AVG(EMPLOYEES.SALARY),
COUNT(EMPLOYEES.SALARY) AS ILOŚĆ_PRACOWNIKÓW
FROM EMPLOYEES, JOBS
WHERE EMPLOYEES.JOB_ID = JOBS.JOB_ID
GROUP BY EMPLOYEES.JOB_ID;
Otrzymujemy tabelę wynikową:
