Reputation: 283
I've been roasting my brain with my limited SQL knowledge while attempting to come up with a query to run a statistic on my orders database.
Table ORDERS is laid out like this:
CustomerID ProductID (etc)
1 10
1 10
1 11
2 10
4 9
Each purchase is recorded with the customer id and the product ID - there CAN be multiple records for the same customer, and even multiple records with the same customer and product.
I need to come up with a query that can return the amount of customers who bought between X and X distinct products - for example, 3 customers bought less then 5 different products, 10 bought from 5-10 different products, 1 bought over 10 different products.
I'm pretty sure this has something to do with derived tables, but advanced SQL is a new fairly craft to me. Any help would be appreciated!
Upvotes: 2
Views: 5086
Reputation: 839114
Try this:
SELECT T1.products_bought, COUNT(T2.cnt) AS total
FROM (
SELECT '<5' AS products_bought, 0 AS a, 4 AS b
UNION ALL
SELECT '5-10', 5, 10
UNION ALL
SELECT '>10', 11, 999999
) T1
LEFT JOIN
(
SELECT COUNT(DISTINCT ProductID) AS cnt
FROM ORDERS
GROUP BY CustomerID
) T2
ON T2.cnt BETWEEN T1.a AND T1.b
GROUP BY a, b
Result:
products_bought total
<5 3
5-10 0
>10 0
Upvotes: 3