AdventureWorks2012: For each customer, determine the number of orders created in year 2007. Show 0 for no order of that customer.

Question

AdventureWorks2012: Sql For each customer, determine the number of orders created in year 2007. If a customer has not created any order in year 2004, show 0 for that customer. Show: customer ID, # of orders created in 2007.

Answer 1

Same approach as in my other response - use a CTE (Common Table Expression) to determine number of sales for each customer in the year 2007:

-- determine the number and total of all sales in 2007
;WITH SalesPerCustomer AS 
(
    SELECT 
        c.CustomerID,
        NumberOfSales = ISNULL(COUNT(soh.SalesOrderID), 0)
    FROM 
        Sales.Customer c 
    INNER JOIN 
        Sales.SalesOrderHeader soh ON soh.CustomerID = c.CustomerID
                                   AND soh.OrderDate >= '20070101' 
                                   AND soh.OrderDate < '20080101'
    GROUP BY    
        c.CustomerID    
)
SELECT 
    CustomerID ,
    NumberOfSales
FROM 
    SalesPerCustomer
ORDER BY 
    NumberOfSales DESC

Ordered the output by number of sales descending, so you'll get the customer with the most sales first