Get the number of rows, including column values ​​in sql server

Question

I need to get the number of query rows, and also get the query columns in one query. The score should be part of the result columns (it should be the same for all rows, since it is shared).

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

If I do this: select a,b,c from table I will get the column values ​​for the query.

I need to get the number and values ​​of columns in one query in a very efficient way.

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

The only way I found is to create a temporary table (using variables), insert the query result, then count, and then return the union of both. But if the result gets thousands of records, it will not be very effective.

Any ideas?

Answer 1

@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

Dividing into a constant makes it count across the entire set of results.

Answer 2

Using CROSS JOIN :

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

Answer 3

SQL Server.

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

Answer 4

:

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

, a, b c