Count same AND different values

user_7480984 0
Viewed 31

At the restaurant, a group of friends (each with their own user_id) ate two or more things (each with their own item_id).

In the database is saved who (user_id) ate what (item_id).

How can I find ONLY who (user_id) has eaten DIFFERENT things (item_id)? In other words, I want to exclude from the results those who ate the same thing twice.

The following query shows me who has eaten more than one thing, without however excluding those who have eaten the same thing twice (which I don't care):

SELECT
    `user_id`, DATE(date), `item_id`, COUNT(*)
FROM `restaurant`
GROUP BY
    `user_id`, DATE(date)
HAVING 
    COUNT(*) > 1

Thanks

count distinct group-by mysql
1 Answers

COUNT(*) counts every row for each group of user_id and DATE(date).

If you want it to count only different item_ids you need the keyword DISTINCT:

SELECT user_id, DATE(date), 
       COUNT(DISTINCT item_id)
FROM restaurant
GROUP BY user_id, DATE(date)
HAVING COUNT(DISTINCT item_id) > 1;

Note that item_id does not make sense in the SELECT list.

user_104988280
Related