Chapter 04 - Grouping and Windowing

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 );