CODEWITHSUNDEEP
mysqlsql
Ringo Blancke
Ringo Blancke

Reputation: 2444

Comparing counts in a SQL where clause

I have two tables: users and photos.

Users have many photos. Photos have a column called user_id, photos have one user. Photos also have a column called reported which is 0 or 1.

I need to know the number of users who have at least 1 photo with reported = 1. I also need to get the number of users who have at least 2 photos with reported = 1.

How would I do this? Here's what I'd like to do, but it obviously doesn't work:

select count(*)
from users join 
 (select * from photos where photos.reported = 1) as p2 
 on users.photo_id = p2.id;

Upvotes: 0

Views: 812

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Just get a histogram of the counts:

select numreported, count(*), min(user_id), max(user_id)
from (select p.user_id, sum(p.reported = 1) as numreported
      from photos p
      group by p.user_id
     ) p
group by numreported
order by numreported;

This gives you the number of users that have all counts of numreported, including 0.

Upvotes: 0

dan b
dan b

Reputation: 1182

Something like the following should work

 select count(hasOne) cntHasOne, count(hasTwo) cntHasTwo from
(select users.user_id, 1  hasOne,
case when count(*) > 1 then  1 else 0 end hasTwo
from users inner join solution on(users.user_id = solution.user_id) 
where solution.winning_status  = 1
group by user_id) T1

Upvotes: 0

SQLChao
SQLChao

Reputation: 7847

This is at least 1

select count(distinct userid)
from photos
where reported = 1

This is at least 2.

select count(distinct userid)
from photos
where reported = 1
group by userid
having count(userid) > 2

Upvotes: 1

Related Questions