Get count of multiple table using Advance SQL
Application Type
Reactive

Hi Team, 


I have two table

Say Materials & Material2

I need to get the count of Materials & Material2 together 

I have tried the below approach , but did not get the result  with joins and searching functionality,

select sum(tbl.AllCount)

    From

    (

   select count(*) as AllCount from Materials

some joins and where conditions

   UNION ALL

   select count(*) as AllCount from Material2

some joins and where conditions

 )tbl;

How can I achieve this ,

Please assist.

Thanks 

Hi Nandini,

Use CTE query will look like below


with cterc as(SELECT COUNT(*) as rn
FROM {Materials}
UNION ALL
SELECT COUNT(*)
FROM {Material2}
)
 SELECT SUM(rn) as totalrowNo
 from cterc


Hi Devendra,


Thanks Much for the response , 

I tried this , its working good , 

but when I use joins and where condition it fails .  not sure why!!

Thanks much, it is working good 

Hey,,
I have implemented same by using 2 different scenario

1.using two aggregate

2. sql

SELECT count(*) as count,(select count(*) from {User_Role}) as count2  from {User}


Output is same 

Hope you will get what you want..
Thanks
Yogesh
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.