05 - Raportimi i te dhenave te grupuara duke perdorur funksionet e grupimit

Ne kete leksion

Shembuj

shembull 1

Per cdo kod departamenti ne tabelen Employees the afishojme:

Zgjidhje

select

department_id,

sum(salary) as Paga_Mujore,

sum(salary*12) as Paga_Vjetore,

min(salary) as Paga_Minimale,

max(salary) as Paga_Maksimale,

avg(salary) as Paga_Mesatare

from employees group by department_id

order by department_id

;

Shembull 2

Per cdo pozicion pune ne tabelen Employees the afishojme:

Zgjidhje

select

job_id,

sum(salary) as Paga_Mujore,

sum(salary*12) as Paga_Vjetore,

min(salary) as Paga_Minimale,

max(salary) as Paga_Maksimale,

avg(salary) as Paga_Mesatare

from employees group by job_id

order by job_id

;

Shembull 3

Grupimi i vlerave te nje shprehje

Pika a

Te afishojme sipas viteve te vjetersise ne pune sa eshte numri i punonjesve, si me poshte

Zgjidhje

select

trunc(months_between(sysdate,hire_date)/12) as vite_pune,

count(*) as Punonjes

from employees

group by trunc(months_between(sysdate,hire_date)/12)

order by vite_pune

;

Shenim:

Vini re klauzolen group by

Pika B

Te shtojme edhe nje analize per pagat:

• Paga maksimale

• paga minimale

• paga mesatare

Zgjidhje

select

trunc(months_between(sysdate,hire_date)/12) as vite_pune,

count(*) as Punonjes,

min(salary) as Paga_Minimale,

max(salary) as Paga_Maksimale,

avg(salary) as Paga_Mesatare

from employees

group by trunc(months_between(sysdate,hire_date)/12)

order by vite_pune

;

Filtrimi - Having vs Where

Rasti 1- Filtojme sipas nje funksioni grupi

Te afishojme vetem dept me shpenzime mujore pagash me te madhe se nje vlere e caktuar

Sakte - perdor having:

select

department_id,

sum(salary) as Paga_Mujore,

sum(salary*12) as Paga_Vjetore,

min(salary) as Paga_Minimale,

max(salary) as Paga_Maksimale,

avg(salary) as Paga_Mesatare

from employees group by department_id

having sum(salary)> &paga

order by department_id

;

Gabim - Perdor where

select

department_id,

sum(salary) as Paga_Mujore,

sum(salary*12) as Paga_Vjetore,

min(salary) as Paga_Minimale,

max(salary) as Paga_Maksimale,

avg(salary) as Paga_Mesatare

from employees

where sum(salary) >&paga

group by department_id

order by department_id

Rasti 2 Filtrojme sipas nje kollone te listes group by

Te afishojme te gjithe departamentet qe ndodhen ne USA:

Sakte perdor where

select

department_id,

sum(salary) as Paga_Mujore,

sum(salary*12) as Paga_Vjetore,

min(salary) as Paga_Minimale,

max(salary) as Paga_Maksimale,

avg(salary) as Paga_Mesatare

from employees

where department_id IN

(

select department_id

from departments

where location_id IN

(

select location_id

from locations

where country_id='US'

)

)

group by department_id

order by department_id

;

Sakte perdor Having

select

department_id,

sum(salary) as Paga_Mujore,

sum(salary*12) as Paga_Vjetore,

min(salary) as Paga_Minimale,

max(salary) as Paga_Maksimale,

avg(salary) as Paga_Mesatare

from employees

group by department_id

having department_id IN

(

select department_id

from departments

where location_id IN

(

select location_id

from locations

where country_id='US'

)

)

order by department_id

;

Rekomandim:

Filtrojme sa me pare te mundemi per efelte te performances. Where filtron para grupimit.

Rasti 3 - Filtrojme sipas nje kollone qe nuk eshte ne listen group by

Shembull te perfshijme ne rezultat vetem punonjesit qe kane me shume se 5 vjet pune

Sakte perdor where

select

department_id,

sum(salary) as Paga_Mujore,

sum(salary*12) as Paga_Vjetore,

min(salary) as Paga_Minimale,

max(salary) as Paga_Maksimale,

avg(salary) as Paga_Mesatare

from employees

where (months_between(sysdate,hire_date)/12)>5

group by department_id

order by department_id

;

gabim perdor having. Kollona hire_date nuk eshte disponibel pas grupimit.

select

department_id,

sum(salary) as Paga_Mujore,

sum(salary*12) as Paga_Vjetore,

min(salary) as Paga_Minimale,

max(salary) as Paga_Maksimale,

avg(salary) as Paga_Mesatare

from employees

group by department_id

having (months_between(sysdate,hire_date)/12)>5

order by department_id

;

Ushtrime shtese

Shembull

Te afishojme listen e shteteve ne te cilet kemi zyra me me shume se 5 punonjes.

Zgjidhje

select * from countries

where country_id IN

(

select Country_id from locations

where location_id

in

(

select LOCATION_ID from DEPARTMENTS

where department_id IN

(

select

department_id

from employees

group by DEPARTMENT_ID

having count(*)>&nr

)

)

)

Ushtrim

Te afishojme te dhenat e punonjesve qe kane nderruar pune me shume se sa nje here.

Udhezim: Punonjesit kodi i te cileve gjendet me shume se 1 here ne job_history

Zgjidhje

select * from employees

where employee_id

IN

(

select employee_id

from job_history

group by employee_id

having count(*)>1

);

Detyre

A- per sejcilin nga pozicionet e punes afishoni nje analize mbi pagat si me poshte:

zgjidhje

B-

(Where apo Having?)

Te dhenat e mesiperme te llogariten vetem per punonjesit

e nivelit te dyte ( dmth qe manager_id IS NOT NULL)

Zgjidhje

C-(Where apo having)

Te dhenat e mesiperme te llogariten vetem per

pozicionet e punes te cilet kane pagen minimale me te madhe se 1000

Zgjidhje