SQL Query find users with only one product type

Question

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.

Answer 1

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