Chapter 04 - Grouping and Windowing

Ky leksion 120 minutesh perfshin faqet 150 - 190 te librit te kursit.

Grouping


Do te grupojme porosite sipas shipperID dhe shippedyear, dhe do te filtrohen vetem ato grupe te cilat kane me pak se 100 porosi. 

SELECT shipperid, YEAR(shippeddate) AS shippedyear,
COUNT(*) AS numorders
FROM Sales.Orders
WHERE shippeddate IS NOT NULL
GROUP BY shipperid, YEAR(shippeddate)
HAVING COUNT(*) < 100;


SELECT shipperid,
COUNT(*) AS numorders,
COUNT(shippeddate) AS shippedorders,
MIN(shippeddate) AS firstshipdate,
MAX(shippeddate) AS lastshipdate,
SUM(val) AS totalvalue
FROM Sales.OrderValues
GROUP BY shipperid;


SELECT shipperid, COUNT(DISTINCT shippeddate) AS numshippingdates
FROM Sales.Orders
GROUP BY shipperid;

Gjenerohet gabim:

SELECT S.shipperid, S.companyname, COUNT(*) AS numorders
FROM Sales.Shippers AS S
JOIN Sales.Orders AS O
ON S.shipperid = O.shipperid
GROUP BY S.shipperid;

Tre menyrat e meposhtme e zgjidhin situaten
SELECT S.shipperid, S.companyname,
COUNT(*) AS numorders
FROM Sales.Shippers AS S
INNER JOIN Sales.Orders AS O
ON S.shipperid = O.shipperid
GROUP BY S.shipperid, S.companyname;


SELECT S.shipperid,
MAX(S.companyname) AS numorders,
COUNT(*) AS shippedorders
FROM Sales.Shippers AS S
INNER JOIN Sales.Orders AS O
ON S.shipperid = O.shipperid
GROUP BY S.shipperid;


WITH C AS
(
SELECT shipperid, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY shipperid
)
SELECT S.shipperid, S.companyname, numorders
FROM Sales.Shippers AS S
INNER JOIN C
ON S.shipperid = C.shipperid;

GROUPING SETS


SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY GROUPING SETS
(
( shipperid, YEAR(shippeddate) ),
( shipperid ),
( YEAR(shippeddate) ),
( )
);

CUBE


SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY CUBE( shipperid, YEAR(shippeddate) );

ROLLUP


SELECT shipcountry, shipregion, shipcity, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY ROLLUP( shipcountry, shipregion, shipcity );


SELECT
shipcountry, GROUPING(shipcountry) AS grpcountry,
shipregion , GROUPING(shipregion) AS grpcountry,
shipcity , GROUPING(shipcity) AS grpcountry,
COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY ROLLUP( shipcountry, shipregion, shipcity );


SELECT GROUPING_ID( shipcountry, shipregion, shipcity ) AS grp_id,
shipcountry, shipregion, shipcity,
COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY ROLLUP( shipcountry, shipregion, shipcity );



Ushtrime


Ndertoni Query qe te shfaqin rezultatet e meposhtme:
1. Gjeni per cdo id kategorie numrin e produkteve.
2. Gjeni per cdo emer kategorie numrin e produkteve.
3. Gjeni score me te larte ne tabelen e scoreve.
4. Gjeni id e studenteve qe kane score me te larte.
5. Gjeni score mesatar ne tabelen e scoreve.
6. Gjeni per secilin testid sasine e studenteve qe e kane zhvilluar testin.
7. Gjeni per secilin testid sasine score maksimal.
8. Gjeni per secilin testid sasine score mesatar.
9. Gjeni per cdo customer sasine e orders qe ka bere.
10. Gjeni per cdo shipcountry sasine e orders qe jane bere nga ai shtet.