total number of items selected

27 Jul 2006 9:27 AM

Tlink

Is it possible to perform a select statement with a top 1000 and still count
how many records meet the criteria without having to select without the top
? Any help would be appreciated.

I am looking to achieve a result where I can inform the user of the real
total number of records that meet the selection criteria in total.

27 Jul 2006 9:39 AM

Uri Dimant

I'm not sure understood your requirements

Could it be?

SELECT TOP 50 COUNT(*)AS cnt,CustomerId FROM Orders
GROUP BY CustomerId

Show quote

"Tlink" <c***@melbournehosting.com> wrote in message
news:ew3aa7VsGHA.4596@TK2MSFTNGP04.phx.gbl...
> Is it possible to perform a select statement with a top 1000 and still
> count how many records meet the criteria without having to select without
> the top ? Any help would be appreciated.
>
> I am looking to achieve a result where I can inform the user of the real
> total number of records that meet the selection criteria in total.
>

27 Jul 2006 9:48 AM

Chris Lim

Uri Dimant wrote:

> I'm not sure understood your requirements

I think he wants to get a count of the total number of rows that would
have been returned by the query if the query didn't have the "TOP x"
clause, and at the same time limiting the rows returned to x rows.

27 Jul 2006 9:44 AM

vinu

hi,

use @@rowcount

e.g

select top 5 *,@@rowcount from tablea

assume tablea contains only 3 rows

1     xxx
2     aaa
3     bbb

running the above sql reurnts the reslut as

1     xxx      3
2     aaa      3
3     bbb     3

thanks

Show quote

"Tlink" <c***@melbournehosting.com> wrote in message
news:ew3aa7VsGHA.4596@TK2MSFTNGP04.phx.gbl...
> Is it possible to perform a select statement with a top 1000 and still
> count how many records meet the criteria without having to select without
> the top ? Any help would be appreciated.
>
> I am looking to achieve a result where I can inform the user of the real
> total number of records that meet the selection criteria in total.
>

27 Jul 2006 9:54 AM

Chris Lim

vinu wrote:
Show quote

> select top 5 *,@@rowcount from tablea
>
> assume tablea contains only 3 rows
>
> 1     xxx
> 2     aaa
> 3     bbb
>
> running the above sql reurnts the reslut as
>
>
> 1     xxx      3
> 2     aaa      3
> 3     bbb     3

I think the OP wants it to return the following if there were 10 rows
in tablea:

1     xxx     10
2     aaa     10
3     bbb     10
4     ccc     10
5     ddd     10

Don't think that's possible using a single query.

27 Jul 2006 9:56 AM

Chris Lim

Tlink wrote:

> Is it possible to perform a select statement with a top 1000 and still count
> how many records meet the criteria without having to select without the top
> ? Any help would be appreciated.
>
> I am looking to achieve a result where I can inform the user of the real
> total number of records that meet the selection criteria in total.

I think you would need to put the full result set into a temp table (or
CTE in SQL Server 2005) and then do your "TOP x" and COUNT(*) from it.
Not good if you are expecting a large result set. Alternatively you
would have to do a separate query to count the full number of rows.