0

So, probably spelled my question wrong, anyway. I'm trying to run a test scenario where some values are duplicated, so they should be 6 but if duplicated they are 12 or 18 and so on,

Currently I'm finding them by having COUNT(*) > 6

but I also want to make sure that the return result would be only those that can be divided by 6 , like 12/6, 18/6 would be returned, but something like 15/6 would not, so I suppose its 'whole' numbers - integer.

select rr.review_id
from review_rating rr
inner join review rw on 
rr.review_id = rw.id
group by review_id
having COUNT(*) > 6 

So, I only want to filter the results whose count can be divided by 6 and the result would be an integer/whole number.

  • HAVING COUNT(*) MOD 6 = 0. – Akina Feb 12 at 12:32
0
select rr.review_id
from review_rating rr
inner join review rw on rr.review_id = rw.id
group by review_id
having MOD(COUNT(*),6)=0
  • Hey, this worked, follow up question - what do I change the 0 to if I want to list only those that cant be divided by 6 – Ruthock Feb 12 at 12:51
  • @Ruthock Simply remove compare and use ... HAVING MOD(COUNT(*),6) ;. – Akina Feb 12 at 13:15
  • @Akina I think I got it working with MOD(COUNT(*),6) = 1 in order to list only the results that are not dividable by 6. I dont really understand what you meant by removing compare? – Ruthock Feb 12 at 13:26
  • WHERE MOD(COUNT(*),6) = 1 will give true for 1,7,13, ... and false for 0,2,3,4,5,6,8,9,... - i.e. not ALL values which cannot be divided by 6 without reminder will be returned. Whereas WHERE MOD(COUNT(*),6) returns true for all values values which cannot be divided by 6 without reminder. Any non-zero value (obtained as a reminder) is treated as true. – Akina Feb 12 at 13:31
  • 1
    Wouldn't MOD(COUNT(*),6) != 0 do the trick? – Ruthock Feb 12 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.