Find the record count of the derived table in SQL Server

Question

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.

Answer 1

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;

Answer 2

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)