GROUP BY and WHERE clause in one query

I am trying to get all rows where the states are greater than 1.

My table:

user_id|state
--------------
1000000|Active
1000000|Created
1000001|Active
1000000|Deleted
1000002|Active
1000001|Created
1000003|Active

My query:

select user_id, count(state) from docs group by user_id order by count(state) desc;

Result is:

user_id | count(state)
1000000 |      3
1000001 |      2
1000002 |      1
1000003 |      1

But I need print only values where is count(state) greater than 1

I am trying this, but it doesn’t work:

select user_id, count(state) from docs where count(state) > 1 group by user_id;

My example: http://www.sqlfiddle.com/#!9/b2716b/2

Answer

HAVING can filter unnecessary rows for you when there is GROUP BY applied. Try this following query for your purpose. WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL

SELECT user_id, 
COUNT(state) 
FROM docs 
GROUP BY user_id
HAVING  COUNT(state) > 1
ORDER BY COUNT(state) DESC;