Mat41 Mat41 - 1 month ago 6
SQL Question

Select TOP x records and return count

I an using SQLServer 2008r2 and have this query:

SELECT top 18 id, obsDate,impact FROM diseaseHits WHERE diseaseID=2 AND pCode=3352 AND obsDate <= '2014/11/5 11:30:00 PM'


which returns:

1
1
0
1
0
0
1
0
1
0
1
0
0
1
1
0
0
1


Now all I want to do is SUM these. Impact is an int. I just cant seem to get it working. I cant do a SELECT TOP 20 COUNT.... so I tried this:

SELECT count(impact) FROM DiseaseHits where DiseaseHits.ID IN(SELECT top 20 id FROM diseaseHits WHERE diseaseID=2 AND pCode=3352 AND obsDate <= '2014/11/5 11:30:00 PM' ORDER by obsDate desc ) ;


but no luck. How can I achieve this?

Thank you

Answer

I think you can just use a subquery:

select sum(impact)
from (select top 18 id, obsDate, impact
      from diseaseHits
      where diseaseID = 2 AND pCode = 3352 AND obsDate <= '2014/11/5 11:30:00 PM'
     ) t;

I would also advise you to use a more standard date format, such as '2014-11-05 11:30:00 PM'.