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
Ushtrim1
Te afishojme sa eshte numri i porosive qe eshte realizuar ne cdo shtet?
select ShipCountry,
COUNT(*) as 'NumerPorosish'
from Orders
group by ShipCountry
order by NumerPorosish DESC
Ushtrim2
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.
select ShipCountry,
COUNT(*) as 'NumerPorosish'
from Orders
group by ShipCountry
having COUNT(*)>80
order by NumerPorosish DESC
Ushtrim3
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.
select ShipCountry,
COUNT(*) as 'NumerPorosish'
from Orders
where OrderDate between '1996-1-1' and '1997-1-1'
group by ShipCountry
having COUNT(*)>20
Ushtrim 4
Te afishojme listen e punonjesve qe kane shitur me shume se 3 porosi ne USA ne peiudhen 1996-1-1 deri 1997-1-1
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
)