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