CODEWITHSUNDEEP
mysqlsqlgoogle-bigquery
user2518176
user2518176

Reputation: 51

Count on a range of values in SQL

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).

Upvotes: 5

Views: 498

Answers (3)

mirkobrankovic
mirkobrankovic

Reputation: 2347

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;

Upvotes: 1

BellevueBob
BellevueBob

Reputation: 9618

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.

Upvotes: 4

Omar Ismail
Omar Ismail

Reputation: 217

I don't believe this is possible using straight SQL without cross-product.

The reason is following - the operation we care about to do collapsing is using "group by". However the way group by works, is it takes multiple rows and collapses them into one row while performing some aggregate calculation.

To do this kind of "one or more" analysis one row needs to be part of the calculation of multiple rows. i.e. the row for the user that has purchased 10 products needs to be part of the rows "1 or more", "2 or more", "3 or more", etc. There's no operation that lets you do this.

Upvotes: -1

Related Questions