coder coder - 5 months ago 8
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

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;