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:

 Department_Id Paga Mujore Paga Vjetore Paga Maksimale Paga Minimale Paga Mesatare
      


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:

 Job_ID Paga Mujore Paga Vjetore Paga Maksimale Paga Minimale Paga Mesatare
      

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

 Vite Pune Punonjes
 10 4


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:

 Job_idPagen Mesatare
(AVG) 
 Pagen Minimale
(MIN)
 Magen Maksimale
(MAX)
 Pagen ne total
(SUM)
     

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