Search code examples
mysqlsqlmysql-error-1054

Where/Having on an aggregate function issue

I am writing a query:

SELECT COUNT( * ) AS  count , var1, var2
FROM  table 
GROUP BY var1, var2
ORDER BY  count DESC

This query works but it grabs everything. I am trying to only get results where count > x (any arbitrary number).

I have tried using WHERE count > x and get:

1054 - Unknown column 'Spammers' in 'where clause'

If I use HAVING count > x (Added it at the very end) I get:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

Other details: using Mysql 4.1

Solution

  • You can use HAVING count(*) > x

    Where x is an integer.

    SELECT COUNT( * ) AS  count , var1, var2
FROM  table 
GROUP BY var1, var2
ORDER BY count(*) DESC 
Having count(*) > x