CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
Mat41
Mat41

Reputation: 1267

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

Upvotes: 0

Views: 105

Answers (2)

tint
tint

Reputation: 33

Try this one,

Is this u want to do.

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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'.

Upvotes: 2

Related Questions