Select a grid territory/sales people/category combos where all line items have more than one quantity

Question

Using AdventureWorks 2008 R2, I want to query a grid that

1. Lists each territory / sales person combination on the vertical axis
2. Lists each subcategory on the horizontal axis
3. Each cell specifies the total number of sales (unique SalesOrderID s) where all the line items in that category / territory / sales person combination have 2 or more quantity

Here's a sample (made up data, I don't know how to query the real stuff!):

                         M.Bikes     Chains     Gloves
Northwest   John Doe     15          4          3
Canada      John Doe     4           2          1
Northwest   Jill Doe     0           5          3
Canada      Jill Doe     1           5          1
etc

I think the following SQL will get me this for mountain bikes (subcat id 1), but I don't know how to add more columns easily. If I start making an independent query for every column, its going to get very slow, very quickly (especially when I have to rejoin all those columns together!)

select
    terr.Name as Territory,
    sp.BusinessEntityID,
    SUM(case when detail.OrderQty > 1 then 1 else 0 end) as MatchingSales

from
    sales.salesorderdetail detail
    inner join sales.SalesOrderHeader header on header.SalesOrderID = detail.SalesOrderID
    inner join sales.SalesPerson sp on sp.BusinessEntityID = header.SalesPersonID
    inner join sales.SalesTerritory terr on terr.TerritoryID = sp.TerritoryID
    inner join sales.SpecialOfferProduct sop on sop.ProductID = detail.ProductID
    inner join Production.Product on Product.ProductID = sop.ProductID
    inner join Production.ProductSubcategory subcat on subcat.ProductSubcategoryID = Product.ProductSubcategoryID

where
    subcat.ProductSubcategoryID = 1 --mountain bikes

Answer 1

I want to preface this by saying I don't have AdventureWorks installed so I can't Check the query... I also don't know what the sales person name column is called so you will more than likely have to change that but here you go, you will just have to add a column at the top for each sub category. I am also assuming the original query is correct.

select
terr.Name as Territory,
sp.Name SalesPerson,
SUM(case when detail.OrderQty > 1 and subcat.ProductSubcategoryID = 1 then 1 else 0 end) as MBikes,
SUM(case when detail.OrderQty > 1 and subcat.ProductSubcategoryID = 2 then 1 else 0 end) as Chains,
SUM(case when detail.OrderQty > 1 and subcat.ProductSubcategoryID = 3 then 1 else 0 end) as Gloves
from
sales.salesorderdetail detail
inner join sales.SalesOrderHeader header on header.SalesOrderID = detail.SalesOrderID
inner join sales.SalesPerson sp on sp.BusinessEntityID = header.SalesPersonID
inner join sales.SalesTerritory terr on terr.TerritoryID = sp.TerritoryID
inner join sales.SpecialOfferProduct sop on sop.ProductID = detail.ProductID
inner join Production.Product on Product.ProductID = sop.ProductID
inner join Production.ProductSubcategory subcat on subcat.ProductSubcategoryID = Product.ProductSubcategoryID
group by terr.Name, sp.Name