25

I would like to ask if there is a way to include the total number of rows, as an additional column, in the returned result sets from a TSQL query using also the Row_Number command.

For example, getting the results set from a query against Book table in a form similar to this:

RowNum   BookId     BookTitle    TotalRows
--------------------------------------------
1        1056       Title1       5    
2        1467       Title2       5    
3        121        Title3       5    
4        1789       Title4       5    
5        789        Title5       5

The query is part of custom paging functionality implemented in a stored procedure. The goal is to return back only the records for the current page Index and limited to the page size, but also the amount of total number of records in the select statement in order to determine the total number of resultset pages.

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
quarkX
  • 391
  • 1
  • 5
  • 6
  • Is this query being issued by an application? If so, can't you just count the number of returned rows in the application? That would be much more efficient. – Daniel Renshaw May 12 '10 at 17:51
  • Pls don't use tabs when posting code in the future - really mucks the formatting. – OMG Ponies May 12 '10 at 17:53
  • @OMGPonies, I'm not saying return the whole table, just the rows that would have been returned anyway and, in fact, they wuld be returned with one fewer columns so that would be *less* data! – Daniel Renshaw May 12 '10 at 17:58
  • @OMG Ponies: No, the dataset would be larger if you add the TotalRows column to the query and send the total row count value once for each row. If you just send the dataset and count the rows in the application, then you avoid repeating the total row count over and over again. Redundant data = slower over the wire (that's bad). – Kelly May 12 '10 at 18:01
  • @OMG Ponies: True if you don't need all the rows it would be less data to send the count with each row, less still to send the count by itself. But from the example provided, it looks like he wants the returned row count. – Kelly May 12 '10 at 18:04
  • @OMGPonies I know what ROW_NUMBER is and that appears to be in column 1 of the resultset, the question appears to be about column 4: the one with the value 5 in every row - 5 being the number of rows returned. – Daniel Renshaw May 12 '10 at 18:05
  • The query is part of custom paging functionality implemented in a stored procedure. The goal Is to return back only the records for the current page Index and limited to the page size, but also the amount of total number of records in the select statement in order to determine the total number of resultset pages. – quarkX May 12 '10 at 18:06
  • @quarkX: There are better means of implementing pagination in SQL Server: http://stackoverflow.com/questions/1897436/row-number-over-not-fast-enough-with-large-result-set-any-good-solution – OMG Ponies May 12 '10 at 18:18
  • @quarkX: since dataset(assuming you are using .NET), why not put the the count in another datatable? dataset can contain many tables. that way, you don't have an extra column in your query and doesn't look unnatural. by the way, where you will store the count value if there's no rows returned? – Hao May 12 '10 at 18:21
  • The problem is how to get the total number count from the same query that returns only the records from the current page. Yes, we can have two separate select statements: one for the total number and one for the records, but they will be very similar and we will repeat the same where clause twice and execute the same select query twice. The idea is to get all the information from one base select ...from query, if we can. – quarkX May 12 '10 at 19:31
  • possible duplicate of [can I get count() and rows from one sql query in sql server?](http://stackoverflow.com/questions/2798094/can-i-get-count-and-rows-from-one-sql-query-in-sql-server) – gbn May 12 '10 at 20:39

7 Answers7

27

In SQL Server 2008 and later, add COUNT(*) OVER () as one of the column names in your query and that will be populated with the total rows returned.

It is repeated in every single row but at least the value is available.

The reason why many other solutions do not work is that, for very large result sets, you will not know the total until after iterating all rows which is not practical in many cases (especially sequential processing solutions). This technique gives you the total count after calling the first IDataReader.Read(), for instance.

select COUNT(*) OVER () as Total_Rows, ... from ...
ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Jon Harbour
  • 402
  • 4
  • 5
  • 2
    Except that this does not usually give you an accurate count. Joining on other tables can screw up the result big time. – marknuzz Oct 23 '15 at 00:28
  • Beware that this can use Segment Operator (in this case it means calculating count for each row) in query execution plan which will slow down the query dramatically.. – yakya Jan 30 '18 at 12:10
17

One can do this with a CTE:

WITH result AS (SELECT ... your query here ...)
SELECT
    *,
    (SELECT COUNT(*) FROM result) AS TotalRows
FROM result;

In general I'd advise against doing this, but if you really need to then this is how to do it.

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Why are you against that? If he has to get the rows in the result, he still would have to run the query again after getting the result, to get the total number of records. – Kamran Ahmed Aug 06 '13 at 09:25
  • It's not ideal to repeat the count that many times. You could just use `@@rowcount` after, or calculate the count in the app – John Gibb Dec 12 '13 at 19:25
  • 1
    This should really be accepted as answer. 4 years later this did help me solve my problem. – Shogoot Oct 27 '14 at 11:52
  • Best answer so far. Works, but my query now is three times slower. – Dherik May 16 '18 at 00:29
4

Via comments attached to the question it's clear that this question relates to paging. In that scenario, there are two broad approaches:

  1. Query all rows that match the search criteria (not just one page worth) and store into a table valued variable (along with a ROW_NUMBER). Then run two queries against that table valued variable: one to extract the desired page of data and the second to to get the total count.
  2. Don't use a table-valued variable and just run the full query twice, once to get the page of data and once for the total count.

The first option works well if the total number of rows is measured in the thousands. If the total number is much higher, you best bet is to run the query twice.

This is a typical space/processing trade-off.

Your milage may vary - what works well for one situation may be terrible in another!

Daniel Renshaw
  • 33,729
  • 8
  • 75
  • 94
  • I don't think ther'll be another way of acheiving this. Is there any? If he has to get the rows in the result, he still would have to run the query again after getting the result, to get the total number of records. – Kamran Ahmed Aug 06 '13 at 09:26
  • Using approach #2, how does one avoid having to repeat the whole `FROM` and `SELECT` sections of the query twice? This can lead to severe code duplication when the joining and filtering is complex. – julealgon Oct 19 '18 at 21:31
  • 1
    @julealgon code duplication can be avoided by putting the common bits of code into a view. – Daniel Renshaw Oct 20 '18 at 06:52
2

Example using the AdventureWorks database

select 
    *, 
    TotalVolume = (select COUNT(*) from HumanResources.Department) 
from  HumanResources.Department
John Sansom
  • 41,005
  • 9
  • 72
  • 84
  • I think this will give an error like `Column 'ColumnA' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` – Mark Byers May 12 '10 at 17:56
  • 2
    Chaps I edited this post after an extreme duuuhhh moment. Sometimes I even surprise myself :-) – John Sansom May 12 '10 at 18:39
  • Works, but for a more complex query you will repeat the same FROM/JOIN/WHERE etc at two places. – Dherik May 16 '18 at 00:05
1

Perhaps what you'er looking for is @@ROWCOUNT?

Richard JP Le Guen
  • 28,364
  • 7
  • 89
  • 119
0
SELECT  n ,
        COUNT(*) OVER ( PARTITION BY 1 )
FROM    ( SELECT    1 AS n
          UNION ALL
          SELECT    2 AS n
        ) AS t

Note that @@ROWCOUNT gives you row count from the previous command. Run this:

SELECT    1 AS n;

SELECT  n ,
        @@ROWCOUNT
FROM    ( SELECT    1 AS n
          UNION ALL
          SELECT    2 AS n
        ) AS t
A-K
  • 16,804
  • 8
  • 54
  • 74
-3
select *, @@rowcount from MyTable
SQLDev
  • 216
  • 2
  • 4