SQLUSA
Free Trial Save on Combos

SQL Server 2005 Best Practices
SQL Server 2008 Best Practices
SQL Server 2000 Best Practices

How to do inner join with a GROUP BY?

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;

 

 

The Best SQL Server Training in the World
SQLUSA.com Home Page