use of multiple sum functions of columns of multiple tables in mysql gives incorrect values

Question

I have a few MySQL tables like below

Table1
Att1 | Flag
xxx  |  1


Table2
Att1 | Flag
xxx  |  2
yyy  |  2


Table3
Att1 | Flag
xxx  |  3
yyy  |  3


Table4
Att1 | Flag
xxx  |  4

Table5
Att1 | Flag
xxx  |  6

Each table has got a few attributes along with the above. I would like to sum flag attributes of each table and VIEW them. MySQL code is below.

create view block as 
select sum(table1.flag)as table1, sum(table2.flag)as table2,
sum(table3.flag) as table3,sum(table4.flag) as table4,
sum(table5.flag) as table5 
from table1,table2,table3,table4,table5;

What i see in my view is:

table1|table2|table3|table4|table5
4     |8     |12    |16    |24

What i actually want to see in my view is:

table1|table2|table3|table4|table5
1     |4     |6     |4     |6

help me! Thanks in advance!

Answer 1

Try this instead:

select table1, table2, table3, table4, table5
from (select sum(table1.flag)as table1) t1 cross join
     (select sum(table2.flag)as table2) t2 cross join
     (select sum(table3.flag)as table2) t3 cross join
     (select sum(table4.flag)as table2) t4 cross join
     (select sum(table5.flag)as table2) t5

Your query is doing a cross join which is a cartesian product among the tables. The best way to avoid this is to do a separate aggregation for each table. You can also do this in the select clause as:

select 
     (select sum(table1.flag)as table1) as t1,
     (select sum(table2.flag)as table2) as t2,
     (select sum(table3.flag)as table2) as t3,
     (select sum(table4.flag)as table2) as t4,
     (select sum(table5.flag)as table2) as t5

Answer 2

Each row from each table is joining with each row from each other table and then you are summing the results. This is not what you want. Try this:

create view block as 
select 'table1' as tableName, sum(flag) from 
table1
UNION ALL
select 'table2' as tableName, sum(flag) from 
table2
UNION ALL
select 'table3' as tableName, sum(flag) from 
table3
UNION ALL
select 'table4' as tableName, sum(flag) from 
table4
UNION ALL
select 'table5' as tableName, sum(flag) from 
table5

Answer 3

The problem with your query is you are doing a cross-join (Cartesian-join) on all five tables. Therefore you are actually summing up:

Att1 | Flag | Att1 | Flag | Att1 | Flag | Att1 | Flag | Att1 | Flag
xxx  |  1   | xxx  |  2   | xxx  | 3    | xxx  |  4   | xxx  | 6
xxx  |  1   | yyy  |  2   | xxx  | 3    | xxx  |  4   | xxx  | 6
xxx  |  1   | xxx  |  2   | yyy  | 3    | xxx  |  4   | xxx  | 6
xxx  |  1   | yyy  |  2   | yyy  | 3    | xxx  |  4   | xxx  | 6
-----+------+------+------+------+------+------+------+------+-----
     |  4   |      |  8   |      | 12   |      |  16  |      | 24

What you want to do instead is sum up the tables individually; the easiest is just to use 5 subqueries:

SELECT (SELECT SUM(Flag) FROM table1) AS table1,
    (SELECT SUM(Flag) FROM table2) AS table2,
    (SELECT SUM(Flag) FROM table3) AS table3,
    (SELECT SUM(Flag) FROM table4) AS table4,
    (SELECT SUM(Flag) FROM table5) AS table5