Vik T. Vik T. - 1 year ago 59
SQL Question

counting selective data for an id

I wanted to know the best way to solve the below scenario, where I've an ID and a state in one table.
Accepted is the end state, once the state reaches accepted for a particular ID no further updates will occur for that ID.

For example, this is how the table looks

ID | State
12 | Not Accepted
12 | Not Accepted
12 | Accepted
45 | Not Accepted
67 | Not Accepted

Now, I want to count distinct ids into Accepted and Not Accepted buckets.

If an
reached the accepted state, then it should be counted in
bucket (ignoring the fact it has "not accepted" states earlier). Example for id = 12 has 2 non accepted states and 1 accepted state so only the count of
should be incremented.

Expected result

Accepted | Not Accepted
1 | 2

I tried is this SQL statement, but it doesn't give me the correct result

SUM(CASE WHEN state = 'Accepted' THEN 1 ELSE 0 END) AS accepted,
SUM(CASE WHEN state != 'Accepted' THEN 1 ELSE 0 END) AS nonaccepted

Results I get:

Accepted | Not Accepted
1 | 4

Answer Source

Not sure what exactly you are asking for but I think you want this:

select sum(case when min_state = 'Accepted' then 1 else 0 end) accepted_cnt,
       sum(case when min_state = 'Not Accepted' then 1 else 0 end) not_accepted_cnt
from (select id, min(state) min_state
      from my_table
      group by id);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download