SQL. Funkcje agregujące Avg, Count, Max, Min, Sum, Nvl

Tabela: pracownicy
pracownik_id
imie
nazwisko
stanowisko_id
wydzial_id
pensja
prowizja

Zadanie 1
Wykonaj raport dotyczący minimalnej, maksymalnej i średniej pensji oraz sumie miesięcznych zarobków hadlowców z działu sprzedaży.

 

Funkcje agregujące

Wykonanie tego raportu wymaga zastosowania operatorów matematycznych na kolumnie pensja i wybranych rekordach tabeli pracownicy.

SQL udostępnia funkcje agregujące, które operują na zbiorze wierszy i zwracają jednen wynik dla grupy.

SELECT AVG(pensja), MAX(pensja), MIN(pensja), SUM(pensja)
FROM pracownicy
WHERE stanowisko_id Like ‚%HAND%’;

AVG
COUNT – zlicza ilość wartości nie NULL
MAX – zwraca maksymalną wartość ignorując wartości NULL
MIN – zwraca minimalną wartość ignorując wartości NULL
SUM – zwraca sumę wartości ignorując wartości NULL

Zadanie 2
Wyświetl pracownika, który pracuje w firmie najdłużej i najkrócej.

SELECT MIN(data_zatrudnienia), MAX(data_zatrudnienia)
FROM pracownicy;

Zadanie 3
Wyświetl liczbę pracowników, którzy otrzymują prowizję w wydziale nr 8

SELECT COUNT (prowizja_proc)
FROM pracownicy
WHERE wydzial_id=8;

Zadanie 4
Utwórz raport, który wyświetli liczbę wydziałów, które zatrudniają pracowników (czyli nie chcesz zliczyć oddziałów, które nie mają pracowników).

SELECT COUNT (DISTINCT wydzial_id)
FROM pracownicy;

Jak pamiętasz funkcje agregujące ignorują wartości NULL.

Zadanie 5
Oblicz średnią prowizję otrzymywaną przez pracowników

SELECT AVG(prowizja_proc)
FROM employess;

Powyższe zapytanie zwróci średnią dla pracowników, którzy otrzymują prowizję, a nie dla wszystkich pracowników. Pamiętaj, że funkcje agregujące ignorują wartości NULL. Jeśli chcesz otrzymać poprawną średnią użyj funkcji Nvl.

Aby zmusić funkcje agregujące do włączenia wartości NULL należy używać funkcji NVL.

Powyższe zapytanie powinno wyglądać tak:
SELECT AVG (NVL(prowizja_proc,0))
FROM pracownicy;

W tym zapytania dla każdego pracownika, który nie otrzymuje prowizji, podstawiana jest wartość 0. Średnia jest wtedy obliczana jako całkowita prowizja wypłacana wszystkim pracownikom podzielona przez ilość wszystkich pracowników w firmie. Średnia jest obliczana na podstawie wszystkich wierszy w tabeli, niezależnie od tego czy wartości null są przechowywane w kolumnie prowizja_proc.

Zadanie 6
Oblicz średnią prowizję dla pracowników w poszczególnych wydziałach.

Aby napisać to zapytanie potrzebujemy czegoś więcej niż tylko funkcji obliczającej średnią, czyli AVG. Musimy, w jakiś sposób wyodrębnić średnią dla pracowników poszczególnych wydziałów, a nie wszystkich pracowników firmy. W tym przypadku potrzebujemy funkcji grupujących wiersze. O funkcjach tych przeczytasz we wpisie Podstawy SQL. Grupowanie wierszy

 

 

 

 

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *