samedi 2 juillet 2016

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.

Aucun commentaire:

Enregistrer un commentaire