TSql Practical SQL Examples (Group By/Compute)

This is the 7th post on practical T-SQL; this post focuses on using group by and compute on aggregation to continue the first one on SELECT, where you would start with… 

Group By

 

SELECT EnglishProductCategoryName,


COUNT(EnglishProductSubcategoryName)
AS [Count Subcategories]

FROM DimProductCategory AS C


INNER
JOIN DimProductSubcategory AS S ON C.ProductCategoryKey = S.ProductCategoryKey

GROUP
BY EnglishProductCategoryName

EnglishProductCategoryName Count Subcategories

————————————————– ——————-

Accessories 12

Bikes 3

Clothing 8

Components 14

N> you can’t have a non-aggregated column and an aggregated column in a column list, unless you include a Group By clause, of course…

Compare with the join without the COUNT

EnglishProductCategoryName Subcategories

————————————————– ————————————————–

Accessories Bike Racks

Accessories Bike Stands

Accessories Bottles and Cages

Accessories Cleaners

Accessories Fenders

Accessories Helmets

Accessories Hydration Packs

Accessories Lights

Accessories Locks

Accessories Panniers

Accessories Pumps

Accessories Tires and Tubes

Bikes Mountain Bikes

Bikes Road Bikes

Bikes Touring Bikes

Clothing Bib-Shorts

Clothing Caps

Clothing Gloves

Clothing Jerseys

Clothing Shorts

Clothing Socks

Clothing Tights

Clothing Vests

Components Handlebars

Components Bottom Brackets

Components Brakes

Components Chains

Components Cranksets

Components Derailleurs

Components Forks

Components Headsets

Components Mountain Frames

Components Pedals

Components Road Frames

Components Saddles

Components Touring Frames

Components Wheels

 

(37 row(s) affected)

 

— group by with distinct

SELECT
DISTINCT


EnglishProductName,
(EnglishProductSubcategoryName)

FROM DimProduct AS C


JOIN DimProductSubcategory AS S

ON C.ProductSubcategoryKey = S.ProductSubcategoryKey

WHERE EnglishProductName =
‘AWC Logo Cap’

–EnglishProductName EnglishProductSubcategoryName

—————————————————- ————————————————–

–AWC Logo Cap Caps

 

SELECT
DISTINCT( EnglishProductName ),
COUNT(EnglishProductSubcategoryName)

FROM DimProduct AS C


JOIN DimProductSubcategory AS S

ON C.ProductSubcategoryKey = S.ProductSubcategoryKey

WHERE EnglishProductName =
‘AWC Logo Cap’

GROUP
BY C.EnglishProductName

–EnglishProductName

—————————————————- ———–

–AWC Logo Cap 3

 

SELECT EnglishProductName, EnglishProductSubcategoryName

FROM DimProduct AS C


JOIN DimProductSubcategory AS S

ON C.ProductSubcategoryKey = S.ProductSubcategoryKey

WHERE EnglishProductName =
‘AWC Logo Cap’

–EnglishProductName EnglishProductSubcategoryName

—————————————————- ————————————————–

–AWC Logo Cap Caps

–AWC Logo Cap Caps

–AWC Logo Cap Caps

Note the difference behavior of distinct using group by an without

 

— having

SELECT EnglishProductCategoryName,


COUNT(EnglishProductSubcategoryName)
AS [Count Subcategories]

FROM DimProductCategory AS C


INNER
JOIN DimProductSubcategory AS S ON C.ProductCategoryKey = S.ProductCategoryKey

GROUP
BY EnglishProductCategoryName

HAVING
COUNT(EnglishProductSubcategoryName)
> 3

You can’t use aliases in Having clauses—you have to repeat the original expression

N > a Where clause must precede a Group by clause; a Having clause has to be after the Group By we-go-ha 8-P

SELECT
COUNT( p.EnglishProductName), s.EnglishProductSubcategoryName , c.EnglishProductCategoryName

FROM dbo.DimProduct AS P

INNER
JOIN dbo.DimProductSubcategory AS S

INNER
JOIN dbo.DimProductCategory AS C

ON S.ProductCategoryKey = C.ProductCategoryKey

ON p.ProductSubcategoryKey = S.ProductSubcategoryKey

GROUP
BY EnglishProductSubcategoryName , EnglishProductCategoryName

N> using an outer join for DimProduct we get the NULL values too

LEFT
OUTER
JOIN
dbo.DimProductSubcategory AS S

INNER
JOIN dbo.DimProductCategory AS C

EnglishProductSubcategoryName EnglishProductCategoryName

———– ————————————————– ————————————————–

209 NULL NULL

 

— avg/ max

SELECT EnglishProductName +
‘ *’+CAST(
COUNT(EnglishProductName)
AS
VARCHAR)
AS EnglishProductName,


MAX(ListPrice)
AS [MaxOfProducts],
MIN(ListPrice)

AS [MinOfProducts],
AVG(ListPrice)
AS [AvgOfProducts]

FROM dbo.DimProduct As S

WHERE ListPrice IS
NOT
NULL

GROUP
BY EnglishProductName

HAVING
COUNT(EnglishProductName)> 1

EnglishProductName MaxOfProducts MinOfProducts AvgOfProducts

———————————————————————————- ——————— ——————— ———————

AWC Logo Cap *3 8.99 8.6442 8.7594

Half-Finger Gloves, L *2 24.49 23.5481 24.019

Half-Finger Gloves, M *2 24.49 23.5481 24.019

Half-Finger Gloves, S *2 24.49 23.5481 24.019

HL Mountain Frame – Black, 38 *3 1349.60 1191.1739 1255.8943

HL Mountain Frame – Black, 42 *3 1349.60 1191.1739 1255.8943

 

— compute

SELECT EnglishProductCategoryName, EnglishProductSubcategoryName,EnglishProductName, ListPrice

FROM DimProductCategory AS C


INNER
JOIN DimProductSubcategory AS S ON C.ProductCategoryKey = S.ProductCategoryKey


INNER
JOIN DimProduct AS P ON S.ProductSubcategoryKey = P.ProductSubcategoryKey

COMPUTE
MAX(ListPrice),
AVG(ListPrice)

EnglishProductCategoryName EnglishProductSubcategoryName EnglishProductName ListPrice

————————————————– ————————————————– ————————————————– ———————

Components Road Frames HL Road Frame – Black, 58 NULL

Components Road Frames HL Road Frame – Red, 58 NULL

Accessories Helmets Sport-100 Helmet, Red 33.6442

Accessories Helmets Sport-100 Helmet, Red 33.6442

Accessories Helmets Sport-100 Helmet, Red 34.99

Accessories Helmets Sport-100 Helmet, Black 33.6442

Accessories Helmets Sport-100 Helmet, Black 33.6442

Accessories Helmets Sport-100 Helmet, Black 34.99

Clothing Socks Mountain Bike Socks, M 9.50

Clothing Socks Mountain Bike Socks, L 9.50

Accessories Helmets Sport-100 Helmet, Blue 33.6442

Accessories Helmets Sport-100 Helmet, Blue 33.6442

Accessories Helmets Sport-100 Helmet, Blue 34.99

Clothing Caps AWC Logo Cap 8.6442

Clothing Caps AWC Logo Cap 8.6442


max avg

——————— ———————

3578.27 747.6617

 

Group By shows the groups and the aggregate calculations such as totals; using the compute we can get the global aggregation with the details

N> the aggreation is done at global level, on all the rows

 

N> The column(s) you use in a Compute By clause must also appear in an Order By clause.

— compute by

SELECT EnglishProductCategoryName, EnglishProductSubcategoryName,EnglishProductName, ListPrice

FROM DimProductCategory AS C


INNER
JOIN DimProductSubcategory AS S ON C.ProductCategoryKey = S.ProductCategoryKey


INNER
JOIN DimProduct AS P ON S.ProductSubcategoryKey = P.ProductSubcategoryKey


ORDER
BY EnglishProductCategoryName

COMPUTE
SUM(ListPrice)

 


sum

———————

295326.3961

 

SELECT EnglishProductCategoryName, EnglishProductSubcategoryName,EnglishProductName, ListPrice

FROM DimProductCategory AS C


INNER
JOIN DimProductSubcategory AS S ON C.ProductCategoryKey = S.ProductCategoryKey


INNER
JOIN DimProduct AS P ON S.ProductSubcategoryKey = P.ProductSubcategoryKey


ORDER
BY EnglishProductCategoryName

COMPUTE
SUM(ListPrice)
BY EnglishProductCategoryName

EnglishProductCategoryName EnglishProductSubcategoryName EnglishProductName ListPrice

————————————————– ————————————————– ————————————————– ———————

Accessories Helmets Sport-100 Helmet,


Accessories Tires and Tubes Touring Tire 28.99

 

sum

———————

1197.9852

 

EnglishProductCategoryName EnglishProductSubcategoryName EnglishProductName ListPrice

————————————————– ————————————————– ————————————————– ———————

Bikes Road Bikes Road-750 Black,


Bikes Road Bikes Road-550-W Yellow, 48 1120.49

 

sum

———————

190574.2218

 

EnglishProductCategoryName EnglishProductSubcategoryName EnglishProductName ListPrice

————————————————– ————————————————– ————————————————– ———————

Clothing Socks Racing Socks,


Clothing Shorts Men’s Sports Shorts, S 59.99

 

sum

———————

2257.1711

 

EnglishProductCategoryName EnglishProductSubcategoryName EnglishProductName ListPrice

————————————————– ————————————————– ————————————————– ———————

Components Road Frames HL Road Frame – Black,


Components Road Frames HL Road Frame – Black, 52 1431.50

 

sum

———————

101297.018

 

 

Now you see the compute by split resuts by EnglishProductCategoryName , and the aggrgations are calcualted on them

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