I have a query as follows:
SELECT TOP 100 *
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.ID = T2.ID
WHERE T1.........
How can I retrieve the count of rows returned by the query excluding the top 100. I want to return the 100 records back as well as the total count of records affected. Is there a simpler way to do it rather than writing the whole query again without the top keyword and including count? eg:
SELECT COUNT(1) AS TableCount
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.ID = T2.ID
WHERE T1.........
Using COUNT(1) OVER() You'll get the no of records in your table in Total_Count column.
SELECT TOP 100 *, COUNT(1) OVER() as 'Total_Count'
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.ID = T2.ID
WHERE T1.........
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With