Execute the following Microsoft SQL Server T-SQL scripts 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 (subquery) and CTE.

-- Basic SQL GROUP BY query - GROUPING on function expression - SUM aggregate function

SELECT YEAR=YEAR(OrderDate),
       TotalSales=SUM(TotalDue)

FROM AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate) -- grouping on computed field

ORDER BY YEAR

/*    YEAR  TotalSales

2001  14327552.2263

2002  39875505.095

2003  54307615.0868

2004  32196912.4165

*/

------------

-- SQL GROUP BY inner join - SQL group by count - sql group by clause

SELECT   Category = Name,

         Color,

         ColorCount,

         AvgListPrice

FROM     (SELECT   ProductSubcategoryID,          -- grouping column

                   Color = COALESCE(Color,'N/A'), -- grouping column with transformation

                   ColorCount = COUNT(* ),        -- aggregate function

                   AvgListPrice = AVG(ListPrice)  -- aggregate function

          FROM     AdventureWorks2008.Production.Product

          GROUP BY ProductSubcategoryID, Color) x -- derived table (subquery)

         INNER JOIN Production.ProductSubcategory psc

           ON psc.ProductSubcategoryID = x.ProductSubcategoryID

ORDER BY Category,

         Color

/* Partial results

Category          Color       ColorCount  AvgListPrice

Mountain Bikes    Black       16          1712.8025

Mountain Bikes    Silver      16          1653.9275

Mountain Frames   Black       14          670.8564

Mountain Frames   Silver      14          685.6507

*/

------------

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

*/

 
-- T-SQL SELECT-ing grouping MAX value using PARTITION BY and ROW_NUMBER
;WITH CTE
     AS (SELECT SalesPersonID,
                TotalDue,
                SalesOrderID,
                OrderDate=CONVERT(Date,OrderDate),
                ROW_NUMBER()
                  OVER(PARTITION BY SalesPersonID ORDER BY TotalDue DESC) AS rn,
                SOCount = COUNT(* )
                            OVER(PARTITION BY SalesPersonID )
         FROM   AdventureWorks2008.Sales.SalesOrderHeader)
SELECT   SalesPersonID,
         TotalDue,
         SOCount,
         SalesOrderID,
         OrderDate
FROM     CTE
WHERE    rn = 1
ORDER BY SalesPersonID;
/*   Partial results     

*/

------------

 
-- SQL GROUP BY query with HAVING on INNER JOINs of tables
SELECT   CR.Name AS Country,
         SP.Name AS State,
         VendorCount = COUNT(*)
FROM     Purchasing.Vendor AS V
         INNER JOIN AdventureWorks.Purchasing.VendorAddress AS VA
           ON VA.VendorID = V.VendorID
         INNER JOIN AdventureWorks.Person.Address AS A
           ON A.AddressID = VA.AddressID
         INNER JOIN AdventureWorks.Person.StateProvince AS SP
           ON SP.StateProvinceID = A.StateProvinceID
         INNER JOIN AdventureWorks.Person.CountryRegion AS CR
           ON CR.CountryRegionCode = SP.CountryRegionCode
GROUP BY CR.Name, SP.Name
HAVING   COUNT(* ) > 2 -- Filter for more than 2 vendors in state
ORDER BY Country, State
/*
Country           State       VendorCount
United States     Arizona     3
United States     California  39
United States     Montana     3
United States     Oregon      13
United States     Washington  25

*/
------------

-- 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

*/