Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

get query count in sql

Tags:

sql

sql-server

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.........
like image 568
RohanDsouza Avatar asked Nov 04 '22 01:11

RohanDsouza


1 Answers

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.........
like image 139
Vishwanath Dalvi Avatar answered Nov 12 '22 11:11

Vishwanath Dalvi