Find the record count of the derived table in SQL Server

user_9445548 0
Viewed 914
CREATE  TABLE   Temp
(
   ID   Int IDENTITY,
   Name Varchar(100)
)

INSERT  INTO    Temp
SELECT  'Elby'
UNION ALL
SELECT  'Eljo'
UNION ALL
SELECT  'Elsy'
UNION ALL
SELECT  'Elsy'
UNION ALL
SELECT  'Eljo'
UNION ALL
SELECT  'Elsy'
UNION ALL
SELECT  'Elsy'

My requied Output is..

    ----------------------------------------
    TotalRecordCount        ID      Name
    ----------------------------------------
    7                       5       Elby
    7                       6       Eljo
    7                       7       Elsy
    7                       8       Elsy
    ----------------------------------------

My query is...

SELECT  TotalRecordCount,
        ID,
        Name        
FROM    (
        SELECT  *
        FROM    Temp
      ) Tab1
WHERE   ID > 4

My question is, how can I find the value for the field 'TotalRecordCount'. It is the total count of the table 'Temp'.

I can not use the query like 'SELECT COUNT(*) FROM Temp AS TotalRecordCount' because it is not a single table.

It is like (SELECT * FROM Table1 JOIN TABLE2 ON (Table1.ID = Table2.ID) JOIN TABLE3 ON (TABLE2.ID = TABLE3.ID) JOIN TABLE4 ON (TABLE3.ID = TABLE4.ID)..... )

For your understanding, I used 'temp' instead of the whole query.

sql-server sql
3 Answers

You can do this using a CTE and a Windowed Function, to avoid the second scan of the table:

WITH Counts AS(
    SELECT ID,
           [Name],
           COUNT(*) OVER () AS TotalRows
    FROM dbo.Temp)
SELECT TotalRows,
       ID,
       [Name]
FROM Counts
WHERE ID > 4;
user_20299830

For fun, you can do this without subqueries or CTEs:

select top (1) with ties t.*, count(*) over () as cnt
from temp t
order by (case when id > 4 then 1 else 2 end)
user_11440350

So 'Temp' in your example is a placeholder for some big complicated query that you don't want to repeat. A Common Table Expression is good for this sort of thing:

WITH cteTemp AS (
    SELECT *
    FROM Temp
)
SELECT (SELECT COUNT(*) FROM cteTemp) AS TotalRecordCount,
      ID,
      Name
FROM cteTemp
WHERE ID > 4
user_221940
Related