Mysql：JOIN 2表，其中一个SUM不正确

Question

This is the structure of the two tables

Table A

+----+-----+----+----------------------+--------------------+----------+
| id | ... |....|   time_start         | time_end           |  total   | 
+----+-----+----+----------------------+--------------------+----------+
  1               2015-12-06 10:00:00    2015-12-06 12:00:00     200
  2               2015-12-07 10:00:00    2015-12-07 12:00:00     300              

Table B

+----+----------+------+------+------+------+
| id | idTableA | val1 | val2 | val3 | val4 |   
+----+----------+------+------+------+------+
  1        1       10     10     10     10
  2        1       10     10     10     10
  3        2       10     10     10     10

The goal is the following : given a time_start and a time_end date , display the SUM of the totals (table A) and the SUM of the val1,val2,val3,val4

Example :

• time_start = 2015-12-01 00:00:00

• time_end = 2015-12-30 23:59:59

Result expected : sum of total = 500 , sum of val(1-4) = 120

I have tried so :

$myquery = "";
$myquery .= "SELECT SUM(tableA.total) AS myTotal,";
$myquery .= "SUM(tableB.val1) + SUM(tableB.val2) + SUM(tableB.val3) + SUM(tableB.val4) AS myValTotal ";
$myquery .= "FROM tableA INNER JOIN tableB ON tableA.id = tableB.idTableA ";
$myquery .= "WHERE tableA.time_start >='".$dateStart."' AND tableA.time_end <='".$dateEnd."'";

The SUM of the val(1-4) is correct , but the SUM of total not.

Answer 1

Aggregate your data before you join, so you don't mistakenly consider values multifold.

select sum(a.total) as mytotal, sum(b.sumval) as myvaltotal
from tablea a
left join
(
  select idtablea, sum(val1+val2+val3+val4) as sumval
  from tableb
  group by idtablea
) b on b.idtablea = a.id
where a.time_start >= @start and a.time_end <= @end;

Here is the same with a subquery in the SELECT clause. It's simpler and circumvents the issue described by Juan Carlos Oropeza in below comments.

select 
  sum(total) as mytotal, 
  sum((
    select sum(val1+val2+val3+val4)
    from tableb
    where idtablea = tablea.id
  )) as sumvaltotal
from tablea
where time_start >= @start and time_end <= @end;