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
select count(*) from (select ...)
select count(select ...)
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)
Incorrect syntax near ')'.
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