T-SQL – Grouping

Single grouping

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

You can use columns which are used in grouping only. If you want to use different column you have two ways:
a) use fake grouping function;
b) group without this column in CTE and then join to CTE.

Multiple grouping

Grouping sets – you can group in a few variants in one time.

SELECT city, region, country, count(*) AS count
FROM hr.employees
GROUP BY GROUPING SETS
(
	(city), -- groups only by city
	(city, region), -- groups by city and region
	(city, region, country), -- groups by city and region and country
	() --take all rows
)

Equally you can use UNION ALL:

SELECT city, NULL, NULL as country, count(*) AS count
FROM hr.employees
GROUP BY city
	UNION ALL
SELECT city, region, NULL as country, count(*) AS count
FROM hr.employees
GROUP BY city, region
	UNION ALL
SELECT city, region, country, count(*) AS count
FROM hr.employees
GROUP BY city, region, country
	UNION ALL
SELECT NULL, NULL, NULL, count(*) AS count
FROM hr.employees

CUBE – groups every combination for given columns. Example below groups by: shipperid, shipyear, (shipperid, shipyear), ().

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

ROLLUP – groups hierarchically. Example below groups by country first, then by region and then by city. Similar result can be achieved by GROUPING SETS, but ROLLUP avoids not needed grouping like grouping the same city from different countries.

SELECT country, region, city, count(*) AS count
FROM hr.employees
GROUP BY ROLLUP (country, region, city)
ORDER BY country, region, city

Pivoting
Pivoting allows you to select what will be in rows’ headers, columns’ headers and which function will be used in crossing cells. Example below shows sum of freight for every customer grouped by city (London or Cork).

WITH PivotingData
AS
(
SELECT 
custid, -- in rows
shipcity, -- in columns
freight -- in cells
FROM Sales.Orders
)
SELECT custid, [London],[Cork]
FROM PivotingData
PIVOT(SUM(freight) FOR shipcity IN ([London],[Cork])) AS P

Unpivoting
Is opposite operation. It translates columns to rows. Having data from previous example it creates set with custid, shipcity and freight columns.

WITH PivotingData
AS
(
SELECT 
custid, -- in rows
shipcity, -- in columns
freight -- in cells
FROM Sales.Orders
),
PivotedData
AS
(
SELECT custid, [London],[Cork]
FROM PivotingData
PIVOT(COUNT(freight) FOR shipcity IN ([London],[Cork])) AS P
)
SELECT custid, shipcity, freight
FROM PivotedData
UNPIVOT (freight FOR shipcity IN ([London],[Cork])) AS U
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s