Do omówienia klauzuli GROUP BY skorzystamy z bezpłatnej bazy Oracle HR dostępnej tutaj:

http://www.oracle.com/technetwork/database/express-edition/downloads/index.html

 Klauzula GROUP_BY jest jedną z najważniejszych narzędzi przy raportowaniu za pomocą języka zapytań SQL Oracle. 

Z połączenia dwóch tabel EMPLOYEES, DEPARTMENTS zrobimy raport miesięcznych wynagrodzeń. Wyświetlimy sumę wynagrodzeń pracowników w poszczególnych działach,  policzymy ilu pracuje tam pracowników i jaka jest ich średnia pensja.

SELECT

DEPARTMENTS.DEPARTMENT_NAME,

SUM(EMPLOYEES.SALARY) AS SUMA_WYNAGRODZEŃ,

ROUND(AVG(EMPLOYEES.SALARY)) AS Średnie_wynagrodzenie,

COUNT(EMPLOYEES.SALARY) AS Liczba_pracowników

FROM EMPLOYEES, DEPARTMENTS

WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

GROUP BY DEPARTMENTS.DEPARTMENT_NAME;

Otrzymujemy tabelę wynikową:

Tym razem chcemy dowiedzieć się jakie są średnie pensje dla poszczególnych stanowisk.

SELECT

JOBS.JOB_TITLE,

SUM(EMPLOYEES.SALARY) AS SUMA_WYNAGRODZEŃ,

ROUND(AVG(EMPLOYEES.SALARY)) AS Średnie_wynagrodzenie,

COUNT(EMPLOYEES.SALARY) AS Liczba_pracowników

FROM EMPLOYEES, JOBS

WHERE EMPLOYEES.JOB_ID = JOBS.JOB_ID

GROUP BY JOBS.JOB_TITLE;

Otrzymujemy tabelę wynikową:

 Uwaga: grupować dane klauzulą GROUP_BY można wyłącznie tymi wartościami (nazwami kolumn), które występują po klauzuli SELECT. 

Teraz postaramy się wyświetlić sumy wynagrodzeń w poszczególnych miastach. Potrzebne będą tabele DEPARTMENTS, LOCATIONS, EMPLOYEES. Aby użyć tej klauzuli trzeba połączyć te trzy kolumny wspólnymi kluczami.

SELECT

LOCATIONS.CITY,

SUM(EMPLOYEES.SALARY) AS SUMA_WYNAGRODZEŃ,

COUNT(EMPLOYEES.SALARY) AS Liczba_pracowników

FROM DEPARTMENTS, LOCATIONS, EMPLOYEES

WHERE DEPARTMENTS.LOCATION_ID = LOCATIONS.LOCATION_ID AND EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

GROUP BY LOCATIONS.CITY;

Otrzymujemy tabelę wynikową:

Teraz sprawdźmy jaka jest suma zarobków w poszczególnych grupach pracowników tak żeby było widać id grupy i nazwę grupy.

SELECT

JOBS.JOB_ID,

JOBS.JOB_TITLE,

SUM(SALARY)

FROM EMPLOYEES,JOBS

WHERE jobs.job_id = EMPLOYEES.JOB_ID

GROUP BY JOBS.JOB_TITLE,JOBS.JOB_ID;

Otrzymujemy tabelę wynikową:

Tym razem użyjemy klauzuli GROUP BY do wyświetlenia wszystkich nazwisk w kolejności alfabetycznej, większych równych od M.

SELECT LAST_NAME

FROM EMPLOYEES

WHERE LAST_NAME > 'M'

GROUP BY LAST_NAME

Otrzymujemy tabelę wynikową:

Podobnie można wyświetlić pensje pracowników powyżej 10 000. Klauzula GROUP BY w obu tych przypadkach może operować tylko na jednej kolumnie.

SELECT

SALARY

FROM EMPLOYEES

WHERE SALARY > 10000

GROUP BY SALARY;

Składnia użycia klauzuli GROUP BY:

 Klauzula GROUP BY z funkcjami agregującymi 

Chcemy dowiedzieć się jakie są sumy wynagrodzeń dla każdego działu naszej firmy.

SELECT

DEPARTMENT_ID,

SUM(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID;

Otrzymujemy tabelę wynikową:

Składnia użycia klauzuli GROUP BY z funkcją agregującą:

 Kolumny wymienione po klauzuli GROUP BY muszą również wystąpić po instrukcji SELECT 

 

 Użycie GROUP BY z klauzulą WHERE 

Klauzula WHERE może ograniczyć działanie formuły GROUP BY do podanego warunku.

Poniżej obliczyliśmy jak jest suma zarobków w poszczególnych działach, uwzględniając wyłącznie pensje powyżej 10 000.

SELECT

DEPARTMENT_ID,

SUM(SALARY)

FROM EMPLOYEES

WHERE SALARY > 10000

GROUP BY DEPARTMENT_ID;

Otrzymujemy tabelę wynikową, sumy tylko dla wynagrodzeń powyżej 10 000.

Teraz podsumowaliśmy wszystkie wynagrodzenia mniejsze niż 8 000. Wynik posortowaliśmy malejąco według uzyskanych sum.

SELECT

DEPARTMENT_ID,

SUM(SALARY) AS Suma_wynagrodzeń

FROM EMPLOYEES

WHERE SALARY < 8000

GROUP BY DEPARTMENT_ID

ORDER BY Suma_wynagrodzeń DESC;

 ORDER BY musi być zawsze umieszczone na końcu zapytania 

Otrzymujemy tabelę wynikową:

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ą: