ashutosh ashutosh - 4 years ago 93
SQL Question

Group by query based on other column value

Suppose I have a table named

process_states
:

+--------+--------+
|Process | State |
-------------------
| A | 0 |
| A | 0 |
| B | 0 |
| B | -1 |
| C | -99 |
-------------------
Note: State can have many more negative and positive state values



I want to find all processes having all rows with state 0. In the above case I want to get A.


I am trying to do it using
group by
, is there a way to do something like this:

select process from process_states
group by process
having <all state for that process is 0>


Is it possible to do it using
group by
?

Answer Source

You could use a group by clause and filter the processes with a having clause:

SELECT   process
FROM     process_states
GROUP BY process
HAVING   COUNT(CASE state WHEN -1 THEN 1 END) = 0

EDIT:

Given the clarifying comments on other answers, if the requirement is to find only processes that only have 0 states, you could count the total number of rows and the number of rows with a 0 state and compare them:

SELECT   process
FROM     process_states
GROUP BY process
HAVING   COUNT(CASE state WHEN 0 THEN 1 END) = COUNT(*)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download