CODEWITHSUNDEEP
sqlsql-server
c-chavez
c-chavez

Reputation: 7496

Get row count including column values in sql server

I need to get the row count of a query, and also get the query's columns in one single query. The count should be a part of the result's columns (It should be the same for all rows, since it's the total).

for example, if I do this: select count(1) from table I can have the total number of rows.

If I do this: select a,b,c from table I'll get the column's values for the query.

What I need is to get the count and the columns values in one query, with a very effective way.

For example: select Count(1), a,b,c from table with no group by, since I want the total.

The only way I've found is to do a temp table (using variables), insert the query's result, then count, then returning the join of both. But if the result gets thousands of records, that wouldn't be very efficient.

Any ideas?

Upvotes: 6

Views: 38636

Answers (4)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

@Jim H is almost right, but chooses the wrong ranking function:

create table #T (ID int)
insert into #T (ID)
select 1 union all
select 2 union all
select 3
select ID,COUNT(*) OVER (PARTITION BY 1) as RowCnt from #T
drop table #T

Results:

ID  RowCnt
1   3
2   3
3   3

Partitioning by a constant makes it count over the whole resultset.

Upvotes: 13

OMG Ponies
OMG Ponies

Reputation: 332531

Using CROSS JOIN:

    SELECT a.*, b.numRows
      FROM YOUR_TABLE a
CROSS JOIN (SELECT COUNT(*) AS numRows
              FROM YOUR_TABLE) b

Upvotes: 6

Jim H.
Jim H.

Reputation: 5579

Look at the Ranking functions of SQL Server.

SELECT ROW_NUMBER() OVER (ORDER BY a) AS 'RowNumber', a, b, c
FROM table;

Upvotes: 1

PaulStock
PaulStock

Reputation: 11263

You could do it like this:

SELECT x.total, a, b, c
 FROM 
    table
    JOIN (SELECT total = COUNT(*) FROM table) AS x ON 1=1

which will return the total number of records in the first column, followed by fields a,b & c

Upvotes: 0

Related Questions