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

*/