Reputation: 716
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
Upvotes: 1
Views: 2606
Reputation: 11
select user_id,state from docs where state = (select state from(select state,count(state) 'count' from docs group by state having count(state)!=1)as temp where temp.state=Docs.state )
Upvotes: 0
Reputation: 952
You can also use the concept of Sub-query. You can read more about sub-query here.
Here in the below query, the inner query will fetch the count of state and their corresponding user_id, whereas the outer query will filter those results to display results where count >=1.
SELECT * FROM (select user_id, count(state) as count from docs group by user_id) WHERE count>1 order by count desc;
Upvotes: 0
Reputation: 16908
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;
Upvotes: 4
Reputation: 231
select *
from (select user_id, count(state) as [count]
from docs
group by user_id)
where count > 1
order by count(state) desc;
Should do the trick
Upvotes: -2
Reputation: 3
Going by your code;
select user_id, count(state) from docs group by user_id order by count(state) desc;
it will be easy and simple to modify.
Try this:
select user_id, count(state) from docs group by user_id where count(state) > 1 order by count(state) desc;
This should work fine and perfect. Your feedback would help me make more research further. Good luck!!!
Upvotes: 0
Reputation: 254
You probably need to use the "having" clause.
select user_id, count(state)
from docs
group by user_id
having count(state) > 1
order by count(state) desc;
Upvotes: 2