how do i return the number of rows selected

SELECT COUNT (fieldname) as Field1

from tablename

or if you want to count distinct rows:

SELECT COUNT (DISTINCT fieldname) as Field1

from tablename

Guys, i found this @@TOTAL_READ  so if we do it:

select t1.name, @@TOTAL_READ as getnumbersofrows from tb_users where ......


it will work for me

I've requested that this question be closed as follows:

Accepted answer: 0 points for rafaelrgl's comment #a39190893

for the following reason:

the answer for the question was not provide

@@TOTAL_READ:  The number of disk reads, not cache reads, by SQL Server since SQL Server was last started.
http://msdn.microsoft.com/en-us/library/ms187917.aspx

It is NOT the same as the number of rows returned in a SQL statement.

Not correct.

You give up too quickly -
select t1.name, t2.cnt as getnumbersofrows from
(select name from tbusers) as t1,
(select count(name) cnt
 from tbusers) as t2
;

yeah, what a rush. but, lets continue, i don't want to perform another select to get the count, is there anyway to do while selecting. i have something like this:


select top 100 t1.* from (select t1.name, getnumbersofrows from tb_users where ......) as t1 where .......

i have one query inside another, so i can't use the @@ROWCOUNT since it will give me 100 rows after i perform this query above, and i want to get the query inside the query, i don't know if i was clear with my writing.

The only other way I can see doing that is to create a user-defined function, getnumbersofrows, to get the total count.

The author has requested the question be closed by accepting a non-solution (their own).

Re-opening to allow the author to accept a valid solution or request the question be deleted.

thanks