Have Geek Brain

SQL Complex Select - Query Formation Query

I have three tables, customers, sales and products.

Sales associates 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 allows me to see the average price for all sales by category. However, I only want to include customers who have more than 3 sales records or more in the DB.

I'm not sure if this is the best way or any other way. Ideas?

+2


a source to share


4 answers


You have not provided customer details anywhere, so I will use that in the sales table

You need to first filter and restrict the sales table by customers having more than 3 sales and then join the product category and get the average across the 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
+4


a source


I would 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
0


a source


I would create a "large customer ids" pseudo table using select and then join it to your query to constrain 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 check this out, so let me know if it works; o)

0


a source


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
0


a source






More articles:


All Articles