SQL: Return paged records and also get count of all records

Question

Here is a simplified version of my query:

select myCol1, mycol2 from MyTable where mycol3 = 'blah'
OFFSET (@skip) rows fetch next (@take) rows only

this works as expected however I'm trying to modify it such that I get the entire count of all found records returned to me as well. Here is my current attempt however DataCount always returns 1 which is not correct. Where am I going wrong?

select t.myCol1, t.mycol2, count(t.id) as DataCount from MyTable t where mycol3 = 'blah'
group by myCol1, myCol2
OFFSET (@skip) rows fetch next (@take) rows only

Answer 1

You can use window functions:

select myCol1, mycol2, count(*) over() dataCount
from MyTable 
where mycol3 = 'blah'
order by ??
offset (@skip) rows fetch next (@take) rows only

Note that your query seems to be missing an order by clause - without it, it is undefined how records are ordered in the resultset, which might make the results inconsistent when the same query is executed more than once (which is likely to happen when you will page).