CODEWITHSUNDEEP
sqlsum
Jim
Jim

Reputation: 923

Sum rows of two tables

I have a Database with two tables:

subscriber_mm
uid_local   | uid_foreign   | uid_partner   
7           |2              |0      
7           |4              |0      
2           |1              |0  
2           |2              |0  
5           |1              |0      
5           |3              |0

partner_mm
uid_local   | uid_foreign   | uid_partner 
7           |1              |1

My goal is to count the total number of rows by uid_local from both tables example: 

count both tables by uid_local = 7

result: 3

example: 

count both tables by uid_local = 2

result: 2

This is my solution (not the best) without the WHERE statement

SELECT sum(
ROWS ) AS total_rows
FROM (
SELECT count( * ) AS ROWS
FROM partner_mm
UNION ALL
SELECT count( * ) AS ROWS
FROM subscriber_mm
) AS u

how can i implement the WHERE statement?

Upvotes: 1

Views: 134

Answers (3)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

No need of doing SUM since you need only to count the total number of rows returned from both tables for particular uid_local. The total rows can be obtained by using UNION ALL operator which clubs the resultset returned from both the tables WITHOUT removing the repeated records

Select count(*) as Result From

(

Select * from subscriber_mm
where uid_local=7
union all
Select * from subscriber_mm
where uid_local=7

)as tmp

Upvotes: 0

code save
code save

Reputation: 1106

Please Try it

    select  uid_local,uid_foreign,uid_partner from subscriber_mm
union
select uid_local,uid_foreign,uid_partner from partner_mm

You can use Union For sum two table rows

Upvotes: 0

Vikram Jain
Vikram Jain

Reputation: 5588

Here pass a value instead of 2 for your query :

select sum(total_count ) as total_count  from 
(select count(*) as total_count from subscriber_mm s where s.uid_local=2
union all 
select count(*) as total_count from partner_mm m where m.uid_local=2) as a

or

 select a.uid_local,sum(total_count ) as total_count  from 
    (select s.uid_local as uid_local, count(*) as total_count from subscriber_mm s group by s.uid_local
    union all 
    select m.uid_local as uid_local, count(*) as total_count from partner_mm m group by m.uid_local) as a  
group by a.uid_local

Upvotes: 1

Related Questions