SQL Server 2014 - returning rows with non-zero values after group-by -

the data looks this:

eqid    date_   close_       high        low          open_   volume    bid          ask       totrtn      _rowid 4   2008-01-02  20.660004   21.100098   20.660004   21.100006   3650    20.649994   21.600006   113.229729  1827 4   2008-01-03  22.979996   22.990005   20.710007   21.100006   13200   21.779999   22.990005   125.944735  1828 4   2008-01-04  22.960007   22.979996   22.000000   22.729996   5600    22.100006   22.960007   125.835183  1829 4   2008-01-07  22.509995   22.949997   22.500000   22.699997   19800   22.520004   22.839996   123.368836  1830 4   2008-01-08  22.389999   22.850006   22.389999   22.500000   8500    22.270004   22.759995   122.711183  1831 4   2008-01-09  22.369995   22.949997   22.369995   22.389999   2100    22.300003   22.500000   122.601548  1832 4   2008-01-10  22.550003   22.619995   22.369995   22.369995   3000    22.399994   22.610001   123.588105  1833 4   2008-01-11  22.710007   22.729996   22.550003   22.729996   1400    22.559998   22.750000   124.465027  1834 4   2008-01-14  22.539993   22.750000   22.470001   22.479996   5700    22.529999   22.699997   123.533244  1835 4   2008-01-15  23.028000   23.028000   22.589996   22.750000   9249    22.750000   23.080002   126.207827  1836 4   2008-01-16  22.919998   23.100006   22.660004   23.000000   10600   22.669998   23.080002   125.615908  1837 4   2008-01-17  22.660004   23.149994   22.660004   23.149994   7325    22.660004   22.919998   124.190979  1838 4   2008-01-18  22.770004   23.089996   22.669998   22.949997   9200    22.770004   22.919998   124.793848  1839 4   2008-01-22  22.279999   22.809998   22.250000   22.509995   8900    22.300003   22.669998   122.108314  1840 4   2008-01-23  22.279999   22.889999   22.270004   22.500000   3900    22.300003   22.759995   122.108314  1841 4   2008-01-24  22.669998   22.910004   22.519897   22.520004   11800   22.550003   22.949997   124.245752  1842 4   2008-01-25  22.770004   22.880005   22.570007   22.690002   6700    22.619995   22.820007   124.793848  1843 

what trying count total number of dates where, given eqid, across defined date range (using statement), volume zero. so, if provide 1 year date range , there 4 days stock did not trade @ want have eqid , zero_count displayed.

the script tried is:

select eqid       ,date_       ,volume       ,count(nullif(volume,0)) hasvol   [crc].[dbo].[eqprice]   date_>'2007-12-31' , date_ < '2012-01-01'   group eqid,date_,volume   order eqid 

but result:

eqid    date_   volume  hasvol 4   2008-01-02 00:00:00 3650    1 4   2008-01-03 00:00:00 13200   1 4   2008-01-04 00:00:00 5600    1 4   2008-01-07 00:00:00 19800   1 4   2008-01-08 00:00:00 8500    1 4   2008-01-09 00:00:00 2100    1 4   2008-01-10 00:00:00 3000    1 4   2008-01-11 00:00:00 1400    1 4   2008-01-14 00:00:00 5700    1 4   2008-01-15 00:00:00 9249    1 4   2008-01-16 00:00:00 10600   1 4   2008-01-17 00:00:00 7325    1 4   2008-01-18 00:00:00 9200    1 4   2008-01-22 00:00:00 8900    1 

i don't seem able figure out syntax total number of days, count (of 1) each day .

any assistance appreciated

edit: moved op's reply here

i ran this:

select top 10 eqid,        date_,        case when sum(coalesce(volume, 0)) = 0 0 else 1 end hasvol   [crc].[dbo].[eqprice]   date_ > '2007-12-31'   , date_ < '2012-01-01'   group eqid, date_   order eqid, date_ 

and got this:

eqid    date_          hasvol 4    2008-01-02 00:00:00    1 4    2008-01-03 00:00:00    1 4    2008-01-04 00:00:00    1 4    2008-01-07 00:00:00    1 4    2008-01-08 00:00:00    1 4    2008-01-09 00:00:00    1 4    2008-01-10 00:00:00    1 4    2008-01-11 00:00:00    1 4    2008-01-14 00:00:00    1 4    2008-01-15 00:00:00    1 

so still not getting 1 single number each eqid on entire date range :-(

edit 2

i thought go, but, missing of instances there no 0 days entire date range. need have daycntwherevolumeiszero show 0 when there reported non-zero volume each day in range. sorry , forth . . .thanks again

the following assumes eqid, date_ combination unique, explained in comments.

select eqid,        date_,        volume,        case when coalesce(volume, 0) = 0 0 else 1 end hasvol   [crc].[dbo].[eqprice]   date_ > '2007-12-31'   , date_ < '2012-01-01'   order eqid, date_ 

if can have duplicate entries of eqid, date_, query want (using group by, sum):

select eqid,        date_,        case when sum(coalesce(volume, 0)) = 0 0 else 1 end hasvol   [crc].[dbo].[eqprice]   date_ > '2007-12-31'   , date_ < '2012-01-01'   group eqid, date_   order eqid, date_ 

edit

after re-reading post, sounds want single row per eqid, , each row, want count of days volume's value 0.

if that's want, query:

(edited make sure when none of days have volume of zero, still row count of zero)

select eqid,        count(case when coalesce(volume, 0) = 0 'x' end) daycntwherevolumeiszero   [crc].[dbo].[eqprice]   date_ > '2007-12-31'   , date_ < '2012-01-01'   group eqid   order eqid