Count number of row from subquery which have data with groupby clause-CodePudding

This is my subquery:

select 
    count(a.ProcessDate),
    b.Market
from
    [dbo].[FileProcessLog] a
left join
    [dbo].[FileMaster] b on a.FileID = b.FileID
where 
    convert(date, a.ProcessDate) = convert(date, getdate()-2)
group by
    b.Market

Now I want number of rows of the result table but when I use below query I got error:

select count(*)
from
    (select count(a.ProcessDate), b.Market
     from [dbo].[FileProcessLog] a
     left join [dbo].[FileMaster] b on a.FileID = b.FileID
     where convert(date, a.ProcessDate) = convert(date, getdate()-2) 
     group by b.Market)

I have tried with alias name as well but it didn't work at all.

Please help finding a solution.

CodePudding user response：

with main as (
select
b.Market,
count(a.ProcessDate) as total
from [dbo].[FileProcessLog] a
LEFT JOIN [dbo].[FileMaster] b
ON a.FileID = b.FileID
where Convert(date, a.ProcessDate) = Convert(date, getdate()-2)
GROUP BY b.Market
) select count(*) as total_rows from main