|
Execute the following
script in Query Editor to demonstrate the application of inner join with a GROUP BY. Two solutions presented: derived table and CTE.
Derived table: when a query is surrounded with parentheses and named; prototype: (SELECT.....) abc.
use AdventureWorks;
go
-- Find total sales for each bike store
select
Store = s.Name,
CustomerNo = s.CustomerID,
TotalSales = TotalDollar
from Sales.Store s
inner join
(
select CustomerID, TotalDollar=SUM(SubTotal)
from Sales.SalesOrderHeader
group by CustomerID
) ts
on s.CustomerID = ts.CustomerID;
/* Partial results
Store CustomerNo TotalSales
A Great Bicycle Company 238 10865.7751
Bike World 23 112601.3212
Functional Store South 687 185735.5823
Eleventh Bike Store 215 62.4874
Gear-Shift Bikes Limited 46 249.5428
*/
-- CTE solution with currency formatting
; with cteStoreTotal as
(
select CustomerID,
TotalDollar='$'+convert(varchar,SUM(SubTotal),1)
from Sales.SalesOrderHeader
group by CustomerID
)
select
Store = s.Name,
CustomerNo = s.CustomerID,
TotalSales = TotalDollar
from Sales.Store s
inner join cteStoreTotal ts
on s.CustomerID = ts.CustomerID;
/* Partial results
Store CustomerNo TotalSales
A Great Bicycle Company 238 $10,865.78
Bike World 23 $112,601.32
Functional Store South 687 $185,735.58
Eleventh Bike Store 215 $62.49
Gear-Shift Bikes Limited 46 $249.54
*/
-- Finding orders related to the maximum AccountNumber within a prefix
-- group by is on the prefix of 7 characters
-- Note: there can be more than one match to the maximum
select soh.*
from Sales.SalesOrderHeader as soh
inner join
(
select max(AccountNumber) as MaxAcct
from Sales.SalesOrderHeader
group by left(AccountNumber, 7)
) as ma
on soh.AccountNumber = ma.MaxAcct
order by soh.AccountNumber
-- Find products with minimum standard cost for specific colors
select ProductName=Name, p.Color, p.ListPrice, msc.MinimumStdCost
from Production.Product p
inner join (
select Color, MIN(StandardCost) as MinimumStdCost
from Production.Product
where Color IN ('Blue', 'Yellow', 'Black')
and StandardCost > 0.0
group by Color) msc
on msc.Color = p.Color
and msc.MinimumStdCost = p.StandardCost
/* Results
ProductName Color ListPrice MinimumStdCost
Sport-100 Helmet, Blue Blue 34.99 13.0863
Half-Finger Gloves, S Black 24.49 9.1593
Half-Finger Gloves, M Black 24.49 9.1593
Half-Finger Gloves, L Black 24.49 9.1593
Short-Sleeve Classic Jersey, S Yellow 53.99 41.5723
Short-Sleeve Classic Jersey, M Yellow 53.99 41.5723
Short-Sleeve Classic Jersey, L Yellow 53.99 41.5723
Short-Sleeve Classic Jersey, XL Yellow 53.99 41.5723
*/
-- CTE - Common Table Expression version
-- Currency formatting for ListPrice and MinimumStdCost
;with cteMSC as
(
select Color, MIN(StandardCost) as MinimumStdCost
from Production.Product
where Color IN ('Blue', 'Yellow', 'Black')
and StandardCost > 0.0
group by Color
)
select
ProductName=Name,
p.Color,
ListPrice = '$'+convert(varchar,p.ListPrice,1),
MinimumStdCost = '$'+convert(varchar,msc.MinimumStdCost,1)
from Production.Product p
inner join cteMSC msc
on msc.Color = p.Color
and msc.MinimumStdCost = p.StandardCost;
|
