Chapter 02 - Reporting aggregated data using group functions

Funksionet e Grupit

Ka disa funksione te SQL qe aplikohen mbi grupet e rreshtave. Keto funksione si psh SUM, COUNT etj, shoqerohen me klauzolen GROUP BY.

Tabela me poshte paraqet nje pershkrim te funksioneve te grupit:

AVG - Mesatarja

COUNT - Numeron vlerat

MAX - maksimumi

MIN - minimumi

SUM - shuma

STDEV - Deviacioni standart (statitike)

VAR - Varianca ( statistike )

Ne rastin e SELECT te shoqeruar me Group By, vetem kollonat qe ndodhen ne listen e GROUP BY mund te jene ne listen e kollonave te SELECT.

Rezultatet pas grupimit mund te filtrohen nepermjet HAVING BY, e cila eshte e ngjashme me WHERE, por ne dallim nga kjo e fundit aplikohet pas llogaritjes se rezultateve mbi rreshtat e grupuara.

Per shembull, komanda e meposhtme afishon numrin e shitjeve per cdo kod punonjesi:

SELECT EmployeeId,

Count(*) ‘NoOfOrdersHeDealed’

from Orders

Group by (EmployeeId)

Shembull

Nga tabela Products

Te afishojme sa produkte

kemi ne magazine gjendje nga cdo kategori

select CategoryID,sum(UnitsInStock )

from Products

group by CategoryID

Te afishojme cmimin mesatar

te produkteve per cdo kategori

select CategoryID,AVG(UnitPrice)

from Products

group by CategoryID

Te afishojme cmimin ma te madh

te produkteve per cdo kategori

select CategoryID,MAX(UnitPrice)

from Products

group by CategoryID

Te afishojme cmimin ma te ulet

te produkteve per cdo kategori

select CategoryID,MIN(UnitPrice)

from Products

group by CategoryID

Te afishojme sa eshte vlera finaciare e magazines per

produktet per cdo kategori

select CategoryID,

SUM(UnitPrice*UnitsInStock ) AS 'VleraFinanciare'

from products

GROUP BY CategoryID

Ti afishojme keto vlera ne nje query

select CategoryID,

COUNT(*) AS 'Produkte',

sum(UnitsInStock )AS 'ProdukteGjendje',

MAX(UnitPrice ) AS 'CmimiMax',

MIN(UnitPrice ) AS 'CmimiMin',

AVG(UnitPrice ) AS 'CmimiMes',

SUM(UnitPrice*UnitsInStock ) AS 'VleraFinanciare'

from products

GROUP BY CategoryID

Where vs Having

Ushtrime

Ushtrim1

Te afishojme sa eshte numri i porosive qe eshte realizuar ne cdo shtet?

Zgjidhje

select ShipCountry,

COUNT(*) as 'NumerPorosish'

from Orders

group by ShipCountry

order by NumerPorosish DESC

Ushtrim 2

Te afishojme sa eshte sasia totale e porosive per cdo produkt?

Zgjidhje

select ProductID,

sum( Quantity ) 'Totali'

from [Order Details]

group by ProductID

order by ProductID

Ushtrim 3

Te afishojme sa eshte numri i punonjesve ne cdo shtet?

Zgjidhje

select Country,

count(*)

from Employees

group by Country

Ushtrim 4

Te afishojme sa eshte numri i punonjesve ne departamentin e shitjeve ne cdo qytet, per te cilin kemi te pakten 2 punonjes?

Zgjidhje

select City,

count(*)

from Employees

where Title like '%Sales%'

group by City

having count(*) >= 2

Ushtrim 5

Te afishojme sa eshte numri i porosive qe eshte realizuar ne cdo shtet, vetem per ato shtet ku kam derguar me shume se 80 porosi.

Zgjidhje

select ShipCountry,

COUNT(*) as 'NumerPorosish'

from Orders

group by ShipCountry

having COUNT(*)>80

order by NumerPorosish DESC

Ushtrim 6

Te afishojme sa eshte numri i porosive qe eshte realizuar ne cdo shtet, vetem per ato shtet ku kam derguar me shume se 20 porosi ne vitin 1996.

Zgjidhje

select ShipCountry,

COUNT(*) as 'NumerPorosish'

from Orders

where OrderDate between '1996-1-1' and '1997-1-1'

group by ShipCountry

having COUNT(*)>20

Ushtrim 7

Te afishojme listen e punonjesve qe kane shitur me shume se 3 porosi ne USA ne peiudhen 1996-1-1 deri 1997-1-1

Zgjidhje

select * from employees

where employeeId

IN

(

select EmployeeID from ORders

where ShipCountry='USA' and

(OrderDate between '1996-1-1' and '1997-1-1')

group by EmployeeID

having COUNT(*)>3

)