DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to construct INNER JOIN with a GROUP BY?

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.

-- SQL JOIN - SQL INNER JOIN - basic syntax - join two tables
-- SQL joining FK to PK - Foreign Key - Primary Key - table / column alias
SELECT Subcategory = ps.Name, ProductName=p.Name, p.ListPrice
FROM AdventureWorks2008.Production.Product p
  INNER JOIN AdventureWorks2008.Production.ProductSubcategory ps
    ON p.ProductSubcategoryID = ps.ProductSubcategoryID
ORDER BY Subcategory, ProductName
/*
Subcategory ProductName             ListPrice
Bib-Shorts  Men's Bib-Shorts, L     89.99
Bib-Shorts  Men's Bib-Shorts, M     89.99
.... */

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

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

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(COALESCE(ListPrice,0.0))  -- 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(CASE WHEN SubTotal IS NULL THEN 0. ELSE SubTotal END)

      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     

SalesPersonID TotalDue SOCount SalesOrderID OrderDate
274 149861.0659 48 51830 8/1/2003
275 198628.3054 450 47395 9/1/2002
276 174496.8155 418 51822 8/1/2003
277 162629.7468 473 46660 7/1/2002

*/

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

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

 

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

 

*/

 

Related articles:

SQL GROUP BY techniques

GROUP BY (Transact-SQL)

http://www.sqlusa.com/articles2005/updatesyntax/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE