Sql: Select count(*) from (select ...)

Question

I have a sql select command with grouping and I want to get the number of total rows. How do I achieve that?

My sql command:

select p.UserName, p.FirstName + ' ' + p.LastName as [FullName]

 ,count(b.billid) as [Count], sum(b.PercentRials) as [Sum] from Bills b

inner join UserProfiles p on b.PayerUserName=p.UserName

 where b.Successful=1

 group by p.UserName, p.FirstName + ' ' + p.LastName

I have tried these with no luck:

select count(*) from (select ...)

and

select count(select ...)

EDIT

this is the complete sql statement that I want to run:

select count(*) from (    select p.UserName, p.FirstName + ' ' + p.LastName as [FullName]

 ,count(b.billid) as [Count], sum(b.PercentRials) as [Sum] from Bills b

inner join UserProfiles p on b.PayerUserName=p.UserName

 where b.Successful=1

 group by p.UserName, p.FirstName + ' ' + p.LastName)

and I get this error on the last line:

Incorrect syntax near ')'.

Answer 1

SELECT COUNT(*)
FROM 
(
select p.UserName, p.FirstName + ' ' + p.LastName as [FullName]
 ,count(b.billid) as [Count], sum(b.PercentRials) as [Sum] from Bills b
inner join UserProfiles p on b.PayerUserName=p.UserName
 where b.Successful=1
 group by p.UserName, p.FirstName + ' ' + p.LastName   --<-- Removed the extra comma here
) A  --<-- Use an Alias here

As I expected from your shown attempt you were missing an Alias

select count(*) 
from (select ...) Q  --<-- This sub-query in From clause needs an Alias

Edit

If you only need to know the rows returned by this query and you are executing this query anyway somwhere in your code you could simply make use of @@ROWCOUNT function. Something like....

SELECT ......     --<-- Your Query

SELECT @@ROWCOUNT    --<-- This will return the number of rows returned 
                       -- by the previous query

Sql: Select count(*) from (select ...)

Sign up