SQL Query find users with only one product type

user_9979557 0
Viewed 594

I solemnly swear I did my best to find an existing question, may I'm not sure how to phrase it correctly.

I would like to return records for users that have quota for only one product type.

| user_id | product |
|       1 |     A   |
|       1 |     B   | 
|       1 |     C   | 
|       2 |     B   | 
|       3 |     B   | 
|       3 |     C   | 
|       3 |     D   |

In the example above I'd like a query that only returns users who carry quota for only one product type - doesn't really matter which product at this point.

I tried using select user_id, product from table group by 1,2 having count(user) < 2 but this does not work, nor does select user_id, product from table group by 1,2 having count(*) < 2

Any help is appreciated.

having count sql
3 Answers

Your having clause is good; the issue's with your group by. Try this:

select user_id
, count(distinct product) NumberOfProducts 
from table 
group by user_id
having count(distinct product) = 1

Or you could do this; which is closer to your original:

select user_id
from table 
group by user_id 
having count(*) < 2
user_3618420

The group by clause can't take ordinal arguments (like, e.g., the order by clause can). When grouping by a value like 1, you're in fact grouping by the literal value 1, which would just be the same for any row in the table, and thus will group all the rows in the table to one group. Since there are more than one product in the entire table, no rows will be returned.

Instead, you should group by the user_id:

SELECT   user_id
FROM     mytable
GROUP BY user_id
HAVING   COUNT(*) = 1
user_24227760

If you want the product, then do:

select user_id, max(product) as product
from table
group by user_id
having min(product) = max(product);

The having clause could also be:

having count(distinct product) = 1
user_11440350
Related