SQL Complex Select - Trouble forming query

Question

I have three tables, Customers, Sales and Products.

Sales links a CustomerID with a ProductID and has a SalesPrice.

select Products.Category, AVG(SalePrice) from Sales 
inner join Products on Products.ProductID = Sales.ProductID
group by Products.Category

This lets me see the average price for all sales by category. However, I only want to include customers that have more than 3 sales records or more in the DB.

I am not sure the best way, or any way, to go about this. Ideas?

Answer 1

I'd try the following:

select Products.Category, AVG(SalePrice) from Sales s
inner join Products on Products.ProductID = s.ProductID
where 
(Select Count(*) From Sales Where CustomerID = s.CustomerID) > 3
group by Products.Category

Answer 2

I'd create a pseudo-table of "big customer IDs" with a select, and then join it to your query to limit the results:

SELECT Products.Category, AVG(SalePrice) FROM Sales
  INNER JOIN Products ON Products.ProductID = Sales.ProductID
  INNER JOIN (
    SELECT CustomerID FROM Sales WHERE COUNT(CustomerID) >= 3 GROUP BY CustomerID
  ) BigCustomer ON Sales.CustomerID = BigCustomer.CustomerID
  GROUP BY Products.Category

Too lazy to test this out though, so let me know if it works ;o)

Answer 3

You haven't mentioned the customer data anywhere so I'll assume it's in the Sales table

You need to filter and restrict the Sales table first to the customers with more the 3 sales, then join to get product category and get the average across categories

select
    Products.Category, AVG(SalePrice)
from
    (SELECT ProductID, SalePrice FROM Sales GROUP BY CustomerID HAVING COUNT(*) > 3) S
    inner join
    Products on Products.ProductID = S.ProductID
group by
    Products.Category

Answer 4

Another way

;WITH FilteredSales AS
(
SELECT Products.Category, Sales.SalesPrice, COUNT(Sales.CustomerId) OVER(PARTITION BY Sales.CustomerId) AS SaleCount
FROM Sales
INNER JOIN Products ON Products.ProductID = Sales.ProductID
)
select Category, AVG(SalePrice)
from FilteredSales
WHERE SaleCount > 3
group by Category