Find Average Sales Amount Per Customer using AdventureWorks2012

Question

I am following a tutorial using MS AdventureWorks2012 and I wanted to write a query to find average sales amount per customer (or in other words, Average Sales for each customer) using AdventureWorks2012. Below is my attempt and it doesn't run. What Am I doing wrong here ?

SELECT  soh.CustomerID AS 'Customer ID'
,p.FirstName + ' ' + p.LastName AS 'Customer Name'
,AVG(soh.TotalDue) AS 'Average Sales Amount Per Customer'
FROM  Sales.SalesOrderHeader AS soh
INNER JOIN Sales.Customer AS c ON c.CustomerID = soh.CustomerID
INNER JOIN Person.BusinessEntityContact AS bec ON bec.PersonID = c.PersonID
INNER JOIN Person.Person AS p ON p.BusinessEntityID = bec.BusinessEntityID
GROUP BY p.FirstName , p.LastName, soh.CustomerID;

Answer 1

Your query runs, it just returns an empty result set.

If you look at BusinessEntityContact, it relates a BusinessEntityID, which is a customer business, to a PersonID, who is a person that is the contact for the business. So if you change your query to this:

SELECT  soh.CustomerID AS 'Customer ID', p.FirstName + ' ' + p.LastName AS 'Customer Name',
    AVG(soh.TotalDue) AS 'Average Sales Amount Per Customer'
FROM  Sales.SalesOrderHeader AS soh
INNER JOIN Sales.Customer AS c ON c.CustomerID = soh.CustomerID
INNER JOIN Person.BusinessEntityContact AS bec ON bec.PersonID = c.PersonID
INNER JOIN Person.Person AS p ON p.BusinessEntityID = bec.PersonID
GROUP BY p.FirstName , p.LastName, soh.CustomerID;

(note the third inner join) You will get 635 rows.