Mysql : JOIN 2 table , one of the SUM is not correct

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.

Only add brackets : $myquery .= "SUM(SUM(tableB.val1) + SUM(tableB.val2) + SUM(tableB.val3) + SUM(tableB.val4)) AS myValTotal "; — Krishna Gupta, Dec 08 '15 at 11:50
please clarify how the sum of total comes, then only it can be done, also sum of values — mugdha, Dec 08 '15 at 11:51
@mugdha tableA.total and tableB.val1,tableB.val2,tableB.val3,tableB.val4 are different type of value. tableA.total is not the sum of the va1,val2,val3,val4 — Whiteboard, Dec 08 '15 at 11:55
I'm guessing you get a total of `700`? `ID` = 1 has two entries in table `B`, so the value of `200` is counted twice. You should probably use a subquery. — HoneyBadger, Dec 08 '15 at 11:58

score 0 · Answer 1 · edited May 23 '17 at 12:23

You have to check what kind of data range overlaps you want, if partial or total.

Determine Whether Two Date Ranges Overlap

Also the best way to test it is copy the query direct on the db first.

Check the data range in the WHERE

SET @dateStart= '2015-12-01 00:00:00';
SET @dateEnd = '2015-12-30 23:59:59';

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

is not a date problem.. check the @HoneyBadger comment , thanks — Whiteboard, Dec 08 '15 at 11:59
Ok, I update my answer. I use your `WHERE` range, but I still belive you should check my link to determinate range overlaps — Juan Carlos Oropeza, Dec 08 '15 at 12:08

score 0 · Answer 2 · answered Dec 08 '15 at 12:06

You shouldn't aggregate on multiplication of rows . Rather aggregate two tables independently and then join them, something like:

select * from
(
SELECT SUM(tableA.total) AS myTotal
FROM tableA 
WHERE tableA.time_start <= @dateEnd 
  AND tableA.time_end >=  @dateStart
) x join (
SELECT SUM(tableB.val1) + SUM(tableB.val2) + 
       SUM(tableB.val3) + SUM(tableB.val4) AS myValTotal 
FROM tableB join  tableA ON tableA.id = tableB.idTableA
WHERE tableA.time_start <= @dateEnd 
  AND tableA.time_end >=  @dateStart
) y;

score 0 · Answer 3 · answered Dec 08 '15 at 12:08

As a starting point, this seems easier to read...

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

Gouda Elalfy · Answer 4 · 2015-12-08T12:26:58.810

0

SELECT  
SUM(tableB.val1) + SUM(tableB.val2) + SUM(tableB.val3) + SUM(tableB.val4) AS myValTotal,
(SELECT SUM(total) from tableA  where   tableA.time_start >='2015-12-01 00:00:00' AND tableA.time_end <= '2015-12-30 23:59:59') as myTotal 
FROM tableA  INNER JOIN tableB ON tableA.id = tableB.idTableA 
WHERE tableA.time_start >='2015-12-01 00:00:00' AND tableA.time_end <= '2015-12-30 23:59:59'

edited Dec 08 '15 at 12:26

answered Dec 08 '15 at 12:11

Gouda Elalfy

6,888
1
26
38

Thorsten Kettner · Accepted Answer · 2015-12-08T20:03:56.453

0

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;

edited Dec 08 '15 at 20:03

answered Dec 08 '15 at 12:14

Thorsten Kettner

89,309
7
49
73

Even when this work and is very simple, You have to be carefull with performace if the tables are big, Because second subquery doesnt have any filter, And join wont use any index. – Juan Carlos Oropeza Dec 08 '15 at 12:23
@Juan Carlos Oropeza: I see no reason for this not to work. However, you are right that the query is written such that the filter is applied only after having aggregated all tableB records. And this could lead to a lot of unnecessary work, if tableB had many records outside the given scope. But usually good DBMS handle this internally and apply the filter (the join criteria) as soon as they can in order not to produce unnecessary overhead. One could make sure by adding `where idtablea in (select id from tablea where time_start ...)`, but as mentioned, a good DBMS does that internally anyhow. – Thorsten Kettner Dec 08 '15 at 12:32
@Juan Carlos Oropeza: Thank you for your remarks anyway. I've added an alternative to my answer which circumvents a large intermediate result altogether. – Thorsten Kettner Dec 08 '15 at 12:37
@ThorstenKettner i tried the second solution , i tried it on phpmyadmin but says : "there is a syntax error...". "SELECT SUM(total) AS mytotal,(SUM(val1+val2+val3+val4) AS sumval FROM tableb WHERE idtablea = tablea.id) AS sumvaltotal FROM tablea WHERE time_start >= '".$dayStart."' and time_end <= '".$dayEnd."'"; – Whiteboard Dec 08 '15 at 12:58
Sorry about that. I had mistakes in both queries (sum(total) instead of merely total). Plus a missing `select` in the second query. I hope it works now. – Thorsten Kettner Dec 08 '15 at 13:05
@ThorstenKettner thanks a lot for the help! but the problem persist : "SELECT total AS mytotal, (SUM(val1+val2+val3+val4) FROM tableb WHERE idtablea = tablea.id) AS sumvaltotal FROM tablea WHERE time_start >= '".$dayStart."' and time_end <= '".$dayEnd."'"; – Whiteboard Dec 08 '15 at 13:12
I edited twice. You missed my second edit, where I added the missing `select`. Sorry. Please copy the query once more or simply insert the `select` before `sum`. – Thorsten Kettner Dec 08 '15 at 13:14
@ThorstenKettner now is correct. I added SUM(total) as mytotal and not total as mytotal, otherwise the total was not correct – Whiteboard Dec 08 '15 at 13:21
Oh, stupid me. You are right. I was so eager to find the error that I over-corrected my queries :-) – Thorsten Kettner Dec 08 '15 at 13:25
@ThorstenKettner no problem , thanks a lot for the help !! Only one thing , can i add a COUNT(tableb.id) in the subquery ? (to know the number of records found in the table b) – Whiteboard Dec 08 '15 at 13:35
For more than one value (e.g. a sum and a count) you'd have to use the first query. The second is for one value only. – Thorsten Kettner Dec 08 '15 at 13:39
i tried this solution for count the tableb result (but it does not work properly) : SELECT SUM(tablea.total) AS mytotal,COUNT(tableb.id) AS numResult, ( SELECT SUM(val1+val2+val3+val4) FROM tableb WHERE tableaid =tablea.id ) AS sumvaltotal FROM tablea LEFT JOIN tableb ON tablea.id = tableb.tableaid WHERE tablea.time_start >= '...' and tablea.time_end <= '....' – Whiteboard Dec 08 '15 at 13:55
Are you sure this doesn't give you wrong sums again? You are joining non-aggregated records again. Anyway, I don't find this a good solution. If you really want to stick with the second query, then simply add a second subquery. But better use the first query, add `, count(*) as cnt` to the subquery and `, sum(b.cnt) as numresult` in the main query. – Thorsten Kettner Dec 08 '15 at 14:02
returning to the main problem, it seems that the sum of the four values is incorrect .. the sums seem that they are not added together – Whiteboard Dec 08 '15 at 15:40
with the first query , the sum of the 4 value is correct , and also the count of the tableb result (so i think i used this solution..) ! with the second query the sum of the 4 value is not correct.. – Whiteboard Dec 08 '15 at 16:56
Oops, a typical MySQL aggregation error, I simply forgot to sum the single results, so MySQL picked one arbitrarily. Sorry once more. I've corrected the query. – Thorsten Kettner Dec 08 '15 at 20:17

score 0 · Answer 6 · answered Dec 08 '15 at 12:26

You can declare variable of int type and store the sum values , and again sumup the stored values to get value total

          declare  @val1 int
          declare @val2 int
          declare @val3 int
          declare @val4 int
          declare @newval int

            select @val1= SUM(isnull(val1,0)) , @val2 =
            sum(isnull(val2,0)), @val3=sum(isnull(val3,0)),@val4 = 
            sum(isnull(val2,0)) from TableB

            select @newval = @val1 +@val2+@val3+@val4

@newval will include the sum of val1 to val4

Mysql : JOIN 2 table , one of the SUM is not correct

6 Answers6