Log in to AnswerLog in to Follow
UserImage.jpg
Nandini i
30Views
4Comments
Solved
Get count of multiple table using Advance SQL
Question
SQL
Reactive
1-Click Publish
Database
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 

0
0
27 May

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
0
0
27 May


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!!

0
0
30 May

Thanks much, it is working good 

0
0
30 May

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
0
0
28 May
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.