Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Please help finding a solution.

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading