coder coder - 1 year ago 51
SQL Question

include zeroes in count for same table

I have table with two columns, one int and another varchar column

SeqId status
int varchar(50)

The status column has 10 statuses, lets say status1, status2, status3, ... status10

I want to write a query to find range of SeqId where status7, status8, status9 count(*) is zero.

Table data,

SeqId Status
1 status1
2 status2
3 status3
4 status4
5 status5
6 status6
7 status7
8 status8
9 status9
10 status10
11 status1
12 status2
13 status3
14 status4
15 status5
16 status9
17 status2
18 status7
19 status3
20 status5
1000 status6

My 1st desired output

When I use range between 15 and 20,

Incorrect query where right now zeroes are not included,

Select status, count(*)
from table1
where seqId between 15 and 20
group by status

**Status Count**
status1 0
status2 1
status3 1
status4 0
status5 2
status6 0
status7 1
status8 0
status9 1
status10 0

Next if possible, I want to find a range where all these 3 statuses, 8, 9, and 10, are zero.

Answer Source

You can use a case statement to turn this into a boolean problem. So:

select t.*, (case when status in ('status8', 'status9', 'status10') then 0 else 1 end) as flag
from t;

You now want to find the longest sequence of zeros. This is a gaps-and-islands problem. One solution is a difference of row numbers to define groups:

select top 1 min(id), max(id), count(*) as length
from (select t.*, 
             (row_number() over (order by id) - row_number() over (partition by flag order by id)) as grp
      from (select t.*, (case when status in ('status8', 'status9', 'status10') then 0 else 1 end) as flag
            from t
           ) t
     ) t
where flag = 0
group by grp, flag
order by count(*) desc;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download