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.