03 Funksionet e grupit
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
)
Ushtrim 5 - Te afishojme kodin dhe vleren financiare te cdo porosie te realizuar ne vitin 1996
OrderId
...
Amount
...
Zgjidhje
Te gjejme kodet e porosive te realizuara ne 1996
select OrderID from orders
where OrderDate
between '1996-1-1' and '1997-1-1'
Zgjidhja perfundimtare
select OrderID,
SUM(UnitPrice*Quantity)as 'Amount'
from [Order Details]
where OrderID IN
(
select OrderID from orders
where OrderDate
between '1996-1-1' and '1997-1-1'
)
group by OrderID
Zgjidhja alternative, jo e rekomandueshme
select OrderID,
SUM(UnitPrice*Quantity)as 'Amount'
from [Order Details]
group by OrderID
having OrderID IN
(
select OrderID from orders
where OrderDate
between '1996-1-1' and '1997-1-1'
)
ndersa komanda e meposhtme afishon kodin e nje porosise dhe vleren financiare te saj si total
SELECT OrderId,SUM(Quantity*UnitPrice) 'totalamount' from [Order Details]
Group by OrderID
Ndersa komanda e meposhtme afishon vetem kodet e punonjesve qe kane bere me shume se 10 shitje:
SELECT EmployeeId,
Count(*) ‘NoOfOrdersHeDealed’
from Orders
Group by (EmployeeId)
Having Count(*) >10
--
shembull
SELECT * FROM Employees
WHERE EmployeeID
IN
(
SELECT DISTINCT EmployeeID FROM Orders
WHERE
OrderID
IN
(
select OrderID 'kodi'
from [Order Details]
where Discount=0 -- para grupimit
group by OrderID
having SUM(Unitprice*Quantity)>12500 -- pas grupimit
)
)
-- FUNKSIONET E GRUPIT
SELECT (KOLLONA)
FROM (TABELE)
WHERE (KUSHT)
GROUP by (KOLLONA)
HAVING (KUSHT)
SELECT Country,City FROM Customers
ORDER BY Country,City
SELECT Country Shteti ,
City 'Qyteti',
COUNT(*) as Numri_Klienteve
FROM Customers
GROUP BY Country,City
order by Country,City
-- TE AFISHOJ VETEM ATO QYTETE KU KAM ME
SHUME SE 2 KLIENTE
SELECT Country Shteti ,
City 'Qyteti',
COUNT(*) as Numri_Klienteve
FROM Customers
GROUP BY Country,City
HAVING COUNT(*)>0
order by COUNT(*)
-- TE AFISHOJ VETEM ATO QYTETE EMRI I SHTETIT TE TE CILIT FILLON ME SHKRONJEN A?
-- KE DO PERDOR ? WHERE APO HAVING?
--NGA ANA SINTAKSORE MUND TI PERDOR TE DYJA
-- TE PROVOJME HAVING
SELECT Country Shteti ,
City 'Qyteti',
COUNT(*) as Numri_Klienteve
FROM Customers
GROUP BY Country,City
HAVING (COUNT(*)>0) AND (Country LIKE 'A%')
order by COUNT(*)
-- TE PROVOJME WHERE
SELECT Country Shteti ,
City 'Qyteti',
COUNT(*) as Numri_Klienteve
FROM Customers
WHERE (Country LIKE 'A%')
GROUP BY Country,City
HAVING (COUNT(*)>0)
order by COUNT(*)
-- CILIN TE PERDORIM???
-- having perdoret per filtrimin e funksioneve te grupit
-- per arsye performance
-- USHTRIM 1
--- GRUPONI TABELEM PUNONJESIT SIPAS TITULLIT TE KORTEZISE
-- AFISHONI NUMRIN E RRESHTAVE PER CDO GRUP
SELECT TitleOfCourtesy,count(*)
FROM Employees
group by TitleOfCourtesy
-- USHTRIM 2
-- SA ESHTE NUMRI I POROSIVE NE CDO SHTET?
select ShipCountry,COUNT(*)
from Orders
Group BY ShipCountry
order by COUNT(*)
--USHTRIM 2b
-- afishoni vetem shtet me me shume se 20 porosi
select ShipCountry,COUNT(*)
from Orders
Group BY ShipCountry
having COUNT(*)>20
order by COUNT(*)
--USHTRIM 2c
-- afishoni vetem shtet me me shume se 20 porosi
--ne periudhen '1996-1-1' deri '1997-1-1'
select ShipCountry,COUNT(*)
from Orders
where OrderDate between '1996-1-1' and '1997-1-1'
Group BY ShipCountry
having COUNT(*)>20
order by COUNT(*)
-- TE AFISHOJME PER CDO POROSI
-- VLEREN FINACIARE TE SAJ
-- SE PARI TE GJEJME VLEREN PER CDO RRESHT
select OrderID,ProductID,
UnitPrice*(1-Discount) as CmimiPasUljes,
(UnitPrice*(1-Discount))* Quantity as 'lineamount'
from [Order Details]
-- ti grupojme sipas kodit te porosise
-- te gjejme shumen e vlerave te rreshtave
select OrderID,
SUM
(
(UnitPrice*(1-Discount))* Quantity
) as 'invoiceamount'
from [Order Details]
Group by OrderID
-- TE SHTOJME NE REZULTAT
--CMIMIN MESATAR TE PRODUKTEVE TE SHITURA NE CDO POROSI
select OrderID,
SUM
(
(UnitPrice*(1-Discount))* Quantity
) as 'invoiceamount',
AVG
(
UnitPrice*(1-Discount)
) 'AverageProductPrice'
from [Order Details]
Group by OrderID
-- TE SHTOJME NE REZULTAT
--CMIMIN ME TE LARTE DHE ME TE ULET
--TE PRODUKTEVE TE SHITURA NE CDO POROSI
select OrderID,
SUM
(
(UnitPrice*(1-Discount))* Quantity
) as 'invoiceamount',
AVG
(
UnitPrice*(1-Discount)
) 'AverageProductPrice' ,
MAX
(
UnitPrice*(1-Discount)
) 'MaxPrice',
MIN
(
UnitPrice*(1-Discount)
) 'MinPrice'
from [Order Details]
Group by OrderID