MySQL Error Code: #1111 - Invalid use of group function [duplicate]

Question

I am trying to write a mysql query to select at least two or more than same user subscription.Here's the query:

  SELECT a.* FROM `subscription` a, user b WHERE b.id=a.user_id and 
        count(a.user_id) > 1 group by a.id

Can anyone tell me what i am doing wrong?

Answer 1

You should use having with Aggregate functions. Where is used to filter rows. Having is used to filter groups based on given condition.

SELECT a.* FROM `subscription` a, user b WHERE b.id=a.user_id 
 group by a.id
having count(a.user_id) > 1

Also, use ANSI Syntax for Join as follows:

SELECT a.* 
FROM `subscription` a 
inner join 
user b 
on b.id=a.user_id 
group by a.id
having count(a.user_id) > 1
;