CODEWITHSUNDEEP
sqlsql-server
Elby Joy
Elby Joy

Reputation: 231

Find the record count of the derived table in SQL Server

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.

Upvotes: 4

Views: 1026

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269813

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)

Upvotes: 2

Thom A
Thom A

Reputation: 95561

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;

Upvotes: 5

codeulike
codeulike

Reputation: 23064

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

Upvotes: 1

Related Questions