CODEWITHSUNDEEP
mysql
Radical_Activity
Radical_Activity

Reputation: 2738

How to exclude specific duplicates from MySQL group_by dataset?

I'm using MySQL 5.7.12. I have a simple query that looks like the following:

SELECT COUNT(id) as count, pic_id
FROM user_pictures 
GROUP BY pic_id

I would like to exclude all the duplicate USER_ID records from grouping. So I did the following, however, it's not returning any result:

SELECT COUNT(id) as count, pic_id 
FROM user_pictures 
GROUP BY pic_id 
HAVING count(user_id) = 1

What am I missing here?

Upvotes: 0

Views: 29

Answers (1)

Barmar
Barmar

Reputation: 780724

Use DISTINCT so it doesn't count duplicates of the same user_id:

SELECT COUNT(id) as count, pic_id 
FROM user_pictures 
GROUP BY pic_id 
HAVING count(DISTINCT user_id) = 1

Upvotes: 1

Related Questions