Count on a range of values in SQL

Question

I have a table that has two columns - Customer ID and number of products they purchased.

What SQL statement would I use to know how many customers purchased 1 or more products, 2 or more products, 3 or more products etc?

Can this be done WITHOUT using Cross Joins (I'm using google bigquery which does not support that).

Answer 1

If you truly need to count the number of customers who purchase "one or more" and "two or more" separately, you'll need a CASE expression:

SELECT SUM(CASE WHEN NumOfProducts >= 1 THEN 1 ELSE 0 END) AS Purchased_1_or_more
     , SUM(CASE WHEN NumOfProducts >= 2 THEN 1 ELSE 0 END) AS Purchased_2_or_more
     , SUM(CASE WHEN NumOfProducts >= 3 THEN 1 ELSE 0 END) AS Purchased_3_or_more
     , SUM(CASE WHEN NumOfProducts >= 4 THEN 1 ELSE 0 END) AS Purchased_4_or_more
     , SUM(CASE WHEN NumOfProducts >= 5 THEN 1 ELSE 0 END) AS Purchased_5_or_more
FROM Customers

And so on for however many categories you want.

Answer 2

Try to use:

SELECT 
CASE NumOfProducts >= 1 THEN 1
WHEN NumOfProducts >= 2 THEN 2
WHEN NumOfProducts >= 3 THEN 3
ELSE 0
END CASE,
COUNT(CustomerID) AS cnt
FROM Customers
GROUP BY CASE NumOfProducts >= 1 THEN 1
WHEN NumOfProducts >= 2 THEN 2
WHEN NumOfProducts >= 3 THEN 3
ELSE 0
END;