How to get result count while paginating the SQL Server query

Question

For displaying the total page number count I need also to retrieve total number of results at the below query. How can I do that ?

Thank you

select 
    AvgLevel, TotalCount, PokemonId, Type1, Speed, MonsterTotalStats 
from 
   (select 
       row_number() over (order by tblPokedex.PokemonId asc) rowNumber,
       AvgLevel, TotalCount, tblPokedex.PokemonId, 
       Type1, tblPokedex.Speed, MonsterTotalStats
    from 
       tblPokemonStats, tblAvailablePokemons, tblPokedex 
    left join 
       tblUsersPokemons on tblPokedex.PokemonId = tblUsersPokemons.PokemonId  
    where 
       tblPokemonStats.PokemonId = tblPokedex.PokemonId 
       and tblPokedex.Class = 'emissary' 
    group by 
       tblPokedex.PokemonId, tblPokedex.Type1, tblPokedex.Speed, 
       tblPokemonStats.AvgLevel, tblPokemonStats.TotalCount, MonsterTotalStats 
   ) result 
where 
    result.rowNumber > 0 and result.rowNumber < 101

thanks for editing @marc_s looks better now

Furkan Gözükara
– Furkan Gözükara

2012-11-30 12:47:42 +00:00
Commented Nov 30, 2012 at 12:47 — Furkan Gözükara
– Furkan Gözükara, Commented Nov 30, 2012 at 12:47

valex · Accepted Answer · 2012-11-30 12:51:13Z

2

Just add column Count(*) over():

..... 
select row_number() 
over (order by tblPokedex.PokemonId asc) rowNumber,
AvgLevel,
Count(*) over() as TotalCount,
.......

see example

select 
AvgLevel,TotalCount,PokemonId,Type1,Speed,MonsterTotalStats, TOTALRECORDCOUNT
from (
select row_number() 
over (order by tblPokedex.PokemonId asc) rowNumber,AvgLevel,TotalCount,tblPokedex.PokemonId,Type1,tblPokedex.Speed,MonsterTotalStats,
count(*) over() as TOTALRECORDCOUNT
from tblPokemonStats,tblAvailablePokemons,tblPokedex left join tblUsersPokemons on tblPokedex.PokemonId=tblUsersPokemons.PokemonId  
where tblPokemonStats.PokemonId=tblPokedex.PokemonId and tblPokedex.Class='emissary' 
group by tblPokedex.PokemonId,tblPokedex.Type1,tblPokedex.Speed,tblPokemonStats.AvgLevel,tblPokemonStats.TotalCount,MonsterTotalStats 
)  
result where result.rowNumber>0 and result.rowNumber<101

edited Nov 30, 2012 at 12:51

answered Nov 30, 2012 at 12:36

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Furkan Gözükara Over a year ago

TotalCount is a parameter that exists on another table that shows how many monsters catched by players.

valex Over a year ago

Ok call it "TotalRecordCount" so total count of records will be in each record you get from this query.

Furkan Gözükara Over a year ago

oh my bad. i saw it wrong. but it displays fetched row count which is 100 while there are total 527 records. so not working as intended.

valex Over a year ago

You should use it in the inner query as in my example then it outputs ALL rows count in inner query before your WHERE in the outer query.

Furkan Gözükara Over a year ago

thanks working great. but your way or this way is better ? stackoverflow.com/questions/4007803/…

|

hermann · Accepted Answer · 2012-11-30 12:37:57Z

1

You coud use :

SELECT @@ROWCOUNT

check it out here.

answered Nov 30, 2012 at 12:37

hermann

6,31511 gold badges48 silver badges66 bronze badges

6 Comments

Furkan Gözükara Over a year ago

how do i add it to the query ?

hermann Over a year ago

it's a separate query, just use it below your query

Furkan Gözükara Over a year ago

i checked and it display the row count retrieved. so it displays 100 not the total number of possible results. Total number is 527 for example.

hermann Over a year ago

What are you trying to retrieve the total of?

Furkan Gözükara Over a year ago

Total available records. I am paginating between records. Like retrieve between 1-100 or 100-200. There is a total number of records which is for example for this query 527. So i also want to obtain that number in this query.

|

Collectives™ on Stack Overflow

How to get result count while paginating the SQL Server query

2 Answers 2

6 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related