Ady96
Ady96

Reputation: 716

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

Upvotes: 1

Views: 2606

Answers (6)

TamilSelvan A
TamilSelvan A

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

Jinesh Shah
Jinesh Shah

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

mkRabbani
mkRabbani

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

Vyron Paschalidis
Vyron Paschalidis

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

Kodex
Kodex

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

Dom
Dom

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

Related Questions