|
Execute the following Microsoft SQL Server T-SQL
script in Management Studio Query Editor to demonstrate the application of inner join and full outer join with GROUP BY queries. Two solutions presented: derived table and CTE.
-- Basic SQL GROUP BY query - SUM aggregate function
SELECT YEAR=YEAR(OrderDate), TotalSales=SUM(TotalDue)
FROM AdventureWorks2008.Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY YEAR
/* YEAR TotalSales
2001 14327552.2263
2002 39875505.095
2003 54307615.0868
2004 32196912.4165
*/
------------
use AdventureWorks;
-- Find total sales for each bike store
-- SQL inner join group by - using group by with joins - SQL derived table - ts
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
*/
/*
Derived table: when a query is surrounded with parentheses and named; prototype: (SELECT.....) abc.
CTE: Common Table Expression; prototype:
;WITH cteOmega AS (SELECT.....); (main SQL query ...cteOmega...)
*/
-- 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
-- Inner join group by
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
-- SQL inner join group by
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
-- SQL inner join common table expression - cte
;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;
------------
-- SQL query to list top 20 dealers of AdventureWorks Cycles
------------
USE AdventureWorks;
-- SQL inner join group by
-- SQL group by inner join
SELECT TOP 20 STORE = S.Name,
-- Currency formatting
TotalSales = '$' + convert(VARCHAR,GC.TotalDollar,1)
FROM Sales.Customer AS C
INNER JOIN Sales.Store AS S
ON S.CustomerID = C.CustomerID
INNER JOIN (SELECT C.CustomerID,
SUM(SOH.SubTotal) AS TotalDollar
FROM Sales.Customer AS C
INNER JOIN Sales.SalesOrderHeader AS SOH
ON C.CustomerID = SOH.CustomerID
WHERE C.CustomerType = 'S' -- stores only
GROUP BY C.CustomerID) AS GC
ON GC.CustomerID = C.CustomerID
ORDER BY TotalDollar DESC
GO
/* Partial results
STORE TotalSales
Vigorous Exercise Company $1,067,744.31
Brakes and Gears $1,067,398.95
Excellent Riding Supplies $1,026,920.76
Totes & Baskets Company $981,392.78
Retail Mall $972,085.34
*/
------------
-- SQL Compare Sales and Purchasing Activity by Week in Month
------------
-- SQL group by week - group by week in month
-- T-SQL common table expression - CTE - multiple CTE query
-- MSSQL row number over partition by
-- SQL full outer join of two group by CTE-s
USE AdventureWorks2008;
WITH Group_By_Week_Purchase_Orders_CTE
AS (SELECT YEAR(OrderDate) AS [YEAR],
MONTH(OrderDate) AS [MONTH],
datepart(week,OrderDate) AS [WEEK],
count(* ) AS POs,
'$' + CONVERT(VARCHAR,sum(TotalDue),1) AS TotalPurchase
FROM Purchasing.PurchaseOrderHeader
GROUP BY YEAR(OrderDate),
MONTH(OrderDate),
datepart(week,OrderDate)),
Group_By_Week_Sales_Orders_CTE
AS (SELECT YEAR(OrderDate) AS [YEAR],
MONTH(OrderDate) AS [MONTH],
datepart(week,OrderDate) AS [WEEK],
count(* ) AS SOs,
'$' + CONVERT(VARCHAR,sum(TotalDue),1) AS TotalSales
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate),
MONTH(OrderDate),
datepart(week,OrderDate))
SELECT so.[YEAR],
so.[MONTH],
Week = ROW_NUMBER()
OVER(PARTITION BY so.YEAR,so.MONTH ORDER BY so.[WEEK]),
so.TotalSales,
SOsReceived = so.SOs,
po.TotalPurchase,
POsIssued = po.POs,
po.[YEAR],
po.[MONTH],
Week = ROW_NUMBER()
OVER(PARTITION BY po.YEAR,po.MONTH ORDER BY po.[WEEK])
FROM Group_By_Week_Purchase_Orders_CTE po
FULL OUTER JOIN Group_By_Week_Sales_Orders_CTE so
ON so.[YEAR]=po.[YEAR] and so.[MONTH]=po.[MONTH] and so.[WEEK]=po.[WEEK]
ORDER BY so.[YEAR],
so.[MONTH],
so.[Week]
GO
/* Partial results
| Year |
Month |
Week |
TotalSales |
SOsReceived |
TotalPurchase |
POsIssued |
Year |
Month |
Week |
| 2003 |
11 |
1 |
$4,668,787.08 |
230 |
$95,348.72 |
4 |
2003 |
11 |
1 |
| 2003 |
11 |
2 |
$281,238.79 |
392 |
$405,320.71 |
16 |
2003 |
11 |
2 |
| 2003 |
11 |
3 |
$296,998.34 |
385 |
$743,391.39 |
44 |
2003 |
11 |
3 |
| 2003 |
11 |
4 |
$315,914.48 |
396 |
$717,539.77 |
28 |
2003 |
11 |
4 |
| 2003 |
11 |
5 |
$360,686.75 |
424 |
NULL |
NULL |
NULL |
NULL |
93 |
| 2003 |
11 |
6 |
$37,557.24 |
62 |
NULL |
NULL |
NULL |
NULL |
94 |
| 2003 |
12 |
1 |
$5,064,196.81 |
587 |
$101,967.90 |
9 |
2003 |
12 |
1 |
| 2003 |
12 |
2 |
$427,217.18 |
495 |
$950,039.55 |
58 |
2003 |
12 |
2 |
| 2003 |
12 |
3 |
$445,661.37 |
470 |
$933,329.09 |
52 |
2003 |
12 |
3 |
| 2003 |
12 |
4 |
$394,935.22 |
442 |
$941,644.80 |
56 |
2003 |
12 |
4 |
| 2003 |
12 |
5 |
$250,822.46 |
278 |
$856,360.62 |
56 |
2003 |
12 |
5 |
| 2004 |
1 |
1 |
$2,337,069.75 |
255 |
$302,020.23 |
8 |
2004 |
1 |
1 |
| 2004 |
1 |
2 |
$332,361.40 |
428 |
$1,102,897.65 |
68 |
2004 |
1 |
2 |
| 2004 |
1 |
3 |
$340,019.44 |
429 |
$898,662.10 |
48 |
2004 |
1 |
3 |
| 2004 |
1 |
4 |
$344,104.20 |
410 |
$1,350,538.60 |
80 |
2004 |
1 |
4 |
| 2004 |
1 |
5 |
$337,458.42 |
424 |
$1,059,836.42 |
56 |
2004 |
1 |
5 |
| 2004 |
2 |
1 |
$4,015,369.35 |
638 |
$1,316,318.94 |
76 |
2004 |
2 |
1 |
| 2004 |
2 |
2 |
$355,946.92 |
421 |
$716,709.84 |
48 |
2004 |
2 |
2 |
| 2004 |
2 |
3 |
$430,084.44 |
499 |
$1,224,653.95 |
65 |
2004 |
2 |
3 |
| 2004 |
2 |
4 |
$351,042.27 |
412 |
$1,137,694.78 |
73 |
2004 |
2 |
4 |
| 2004 |
2 |
5 |
$54,739.53 |
62 |
$176,506.48 |
8 |
2004 |
2 |
5 |
*/
|