Sum rows of two tables

Question

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?

- PHP-Version: 4.4.9 - Server Version: 5.5.28 - ENGINE=MyISAM DEFAULT CHARSET=utf8; — Jim
– Jim, Commented Feb 12, 2014 at 11:10

Vikram Jain · Accepted Answer · 2014-02-12 11:12:29Z

1

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

answered Feb 12, 2014 at 11:12

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

code save · Accepted Answer · 2014-02-12 11:52:50Z

0

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

edited Feb 12, 2014 at 11:52

answered Feb 12, 2014 at 11:44

code save

1,1061 gold badge9 silver badges15 bronze badges

Comments

Mudassir Hasan · Accepted Answer · 2014-02-12 12:15:39Z

0

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

edited Feb 12, 2014 at 12:15

answered Feb 12, 2014 at 11:16

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

Collectives™ on Stack Overflow

Sum rows of two tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related