mysql query to find rows having only single specific sub-category against any category (in group by-CodePudding

I have MySQL table with name users. SQL returns following result

select user_id, vegetarian, count(*) from users group by user_id, vegetarian;

Current SQL Output

user_id   vegetarian      count(*)
2         1               15
3         0               131
3         1               6
4         1               6
5         0               113
5         1               7
6         1               6
7         0               107
7         1               11

Required Output My required output is:

user_id   vegetarian      count(*)
2         1               15
4         1               6
6         1               6

Kindly help me for SQL query to get my required output.

Kind Regards

CodePudding user response：

SELECT user_id, 1 vegetarian, COUNT(*) 
FROM users 
GROUP BY user_id
HAVING NOT SUM(NOT vegetarian);

NOT vegetarian converts 0 to 1 and backward. Hence SUM() calculates the amount of rows which are not vegetarians. And HAVING NOT SUM() removes the rows where this sum is above zero.

CodePudding user response：

Thanks for reply.

I am looking for rows where vegetarian = 1 ... But if there are having both vegetarian = 0 and vegetarian = 1 then those rows should not be displayed.

Like user_id=3 should not be included in output because user_id=3 has both vegetarian = 0 and vegetarian = 1:

3 0 131 3 1 6

user_id=4 should be included in output because user_id=4 has only single option where vegetarian = 1:

4 1 6

Regards