CODEWITHSUNDEEP
sqlsql-server
RohanDsouza
RohanDsouza

Reputation: 412

get query count in sql

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

Upvotes: 5

Views: 194

Answers (1)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36671

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

Upvotes: 2

Related Questions