SQL Query Multiple Joins Unexpected Results

Question

My task is to write a query that will return sales information for each customer category and year. The columns required in the result set are:

• OrderYear - the year the orders were placed
• CustomerCategoryName - as it appears in the table Sales.CustomerCategories
• CustomerCount - the number of unique customers placing orders for each CustomerCategoryName and OrderYear
• OrderCount - the number of orders placed for each CustomerCategoryName and OrderYear
• Sales - the subtotal from the orders placed, calculated from Quantity and UnitPrice of the table Sales.OrderLines
• AverageSalesPerCustomer - the average sales per customer for each CustomerCategoryName and OrderYear

The results should be sorted in ascending order, first by order year, then by customer category name.

My attempt at a solution:

SELECT 
    CC.CustomerCategoryName,
    YEAR(O.OrderDate) AS OrderYear,
    COUNT(DISTINCT C.CustomerID) AS CustomerCount,
    COUNT(DISTINCT O.OrderID) AS OrderCount,
    SUM(OL.Quantity * OL.UnitPrice) AS Sales,
    SUM(OL.Quantity * OL.UnitPrice) / COUNT(DISTINCT C.CustomerID) AS AverageSalesPerCustomer
FROM
    Sales.CustomerCategories CC
INNER JOIN 
    Sales.Customers C ON C.CustomerCategoryID = CC.CustomerCategoryID
INNER JOIN 
    Sales.Orders O ON O.CustomerID = C.CustomerID
INNER JOIN 
    Sales.OrderLines OL ON OL.OrderID = O.OrderID
GROUP BY 
    CC.CustomerCategoryName, YEAR(O.OrderDate)
ORDER BY 
    YEAR(O.OrderDate), CC.CustomerCategoryName;

My OrderCount seems correct. However, I don't believe my CustomerCount is correct and my Sales and AverageSalesPerCustomer seem way off. The Categories that do not have any customers and orders do not show up in my results.

Is the reason that my counts are off and that he categories that do not have any customers are omitted is because they only have null values? I believe the question is looking for all the categories.

I am using the sample tables of WideWorldImporters from Microsoft.

Any help would be appreciated as I am new to SQL and Joins are a very hard concept for me to understand.

Answer 1

Presently, you're getting only the data that exists in order details...and not getting anything for the non-existent orders. Normally, this is accomplished with outer joins instead of inner joins, and an isnull(possiblyNullValue,replacementValue).

Also, while you're grouping by year(o.OrderDate), your join for orders isn't distinguishing by year...probably getting all years worth of data for each customer for each reporting period.

So, let's get the reporting period out first...and make sure we're basing our results on that:

select distinct year(o.OrderDate) from Sales.Orders

But really, you want all categories and all years...so you can combine them to get the real basis:

select
  cc.CustomerCategoryId,
  cc.CustomerCategoryName, 
  year(o.OrderDate)
from 
  Sales.Orders o
    cross join
  Sales.CustomerCategories cc
group by
  cc.CustomerCategoryId,
  cc.CustomerCategoryName, 
  year(o.OrderDate)

Now, you want to join this mess into the remaining query. There are two ways to do this...one is to use a with clause...but sometimes it's just easier to just wrap the basis query up in parentheses and use it as if it was a table:

select
  cy.CustomerCategoryName,
  cy.CalendarYear,
  count(distinct c.CustomerId) CustomerCount,
  isnull(sum(ol.UnitPrice * ol.Quantitiy),0.0) Sales,
  isnull(sum(ol.UnitPrice * ol.Quantitiy) / count(distinct c.CustomerId),0.0) AverageSalesPerCustomer
from
  (
    select
      cc.CustomerCategoryId,
      cc.CustomerCategoryName, 
      year(o.OrderDate) CalendarYear --> must name calc'd cols in virtual tables
    from 
      Sales.Orders o
        cross join
      Sales.CustomerCategories cc
    group by
      cc.CustomerCategoryId,
      cc.CustomerCategoryName, 
      year(o.OrderDate)
  ) as cy --> cy is the "Category Years" virtual table
    left outer join
  Sales.Customers c
    on cy.CustomerCategoryId = c.CustomerCategoryId
    left outer join
  Sales.Orders o
    on 
      c.CustomerId = o.CustomerId          --> join on customer and year
      and                                  --> to make sure we're only getting 
      cy.CalendarYear = Year(o.OrderDate)  --> orders in the right year
    left outer join
  Sales.OrderLines ol
    on o.OrderId = ol.OrderId
group by
  cy.CalendarYear,
  cy.CustomerCategoryName
order by
  cy.CalendarYear,
  cy.CustomerCategoryName

By the way...get comfortable messing with your queries to select some subset...for example, you can add a where clause to select only one company...and then go have a look at the details...to see if it passes the smell test. It's a lot easier to evaluate the results when you limit them. Similarly, you can add the customer to the select list and the outer grouping for the same reason. Experimentation is the key.