SQL select with GROUP BY and COUNT?

0
votes

Let's say I have three tables to manage purchases from my online shop:

  • Products: with columns ID, Name, Price
  • Customers: with columns ID, Name
  • Purchases: with columns ProductID, CustomerID, PurchaseDate

Now, how would I go about retrieving products purchased by more than N distinct customers?

I've tried the following on SQL Server 2019 Trial Edition but I'm getting a syntax error on COUNT.

SELECT ProductID, CustomerID, COUNT(*) as C    
FROM Purchases    
GROUP BY ProductID, CustomerID    
HAVING C > 100    
ORDER BY C DESC

Better still, how would I go about retrieving products purchased by more than N distinct customers over a 30-day period?

Thanks for any help and/or pointers.

4
sqlsql-servertsql
Your current schema doesn't support this, you are not storing timestamps of purchases. In present scenario there is no way to filter purchases on data range. - Anmol Batra
Please post the exact error message - derpirscher

4 Answers

1
votes

With your current query, you are just counting how often each customer bought each product, because you are grouping by the combination of productid and customerid. Furthermore you cannot reference to the column alias for the count in the HAVING or ORDER BY clause

Try this

declare @purchasedatelower datetime = dateadd(day, -30, getdate())
declare @purchasedateupper datetime = getdate()
declare @distinctcustomers int = 100

select productid, count(distinct customerid) as customercount
from purchases
where purchasedate between @purchasedatelower and @purchasedateupper
group by productid
having count (distinct customerid) >= @distinctcustomers
order by count(distinct customerid) desc

This will return all products, which have been bought by at least 100 distinct customers, in the last 30 days, together with the distinct number of customers.

0
votes

Try to order them by COUNT()

SELECT ProductID, CustomerID

FROM Purchases

GROUP BY ProductID, CustomerID

HAVING COUNT(*) > 100

ORDER BY COUNT(*) DESC
0
votes

Now, how would I go about retrieving products purchased by more than N distinct customers?

You would use COUNT(DISTINCT):

SELECT ProductID, COUNT(DISTINCT CustomerID) as num_customers   
FROM Purchases    
GROUP BY ProductID    
HAVING COUNT(DISTINCT CustomerID) > 100    
ORDER BY COUNT(DISTINCT CustomerID) DESC;

If you have a particular period in mind, then add a WHERE clause before the GROUP BY.

0
votes
DECLARE @Popularity int = 1
DECLARE @MonthsAgo int = 1

SELECT p.ProductId, p.ProductName, pc.CustomersDistinct
FROM (
    -- Sub-query to compute the aggregate.
    -- Count the number of distinct customers that purchased each product.
    SELECT ProductId, COUNT(DISTINCT CustomerId) AS CustomersDistinct
    FROM Purchases
    WHERE PurchaseDate >= DATEADD(MONTH, -@MonthsAgo, CURRENT_TIMESTAMP)
    GROUP BY ProductId
    HAVING COUNT(DISTINCT CustomerId) > @Popularity
) AS pc
    -- Now do JOINs to look up helpful values for the final SELECT list.
    INNER JOIN Products p ON pc.ProductId = p.ProductId
ORDER BY 3 DESC