CODEWITHSUNDEEP
mysql
Lurk21
Lurk21

Reputation: 2337

MySQL SUM() giving unexpected answer

I have the following SQL:

SELECT
          t.amount
        FROM
          transactions t
        JOIN contracts c ON t.contractId = c.id
        JOIN insertions i ON c.id = i.contractId
        JOIN magazines m ON i.magazineId = m.id
        WHERE m.id = 26
          AND t.isChargedBack IS FALSE
          AND t.`timestamp` >= '2013-09-12'
          AND t.`timestamp` <= date_add('2013-09-12',interval 1 month)
        GROUP BY 
          t.id;

Which yields:

1100
800
1025
500
1200
552
395
395
1170
1000
675

I want only the SUM of this result. I expect 8812.

So I use the following SQL:

SELECT
          IFNULL(SUM(t.amount),0)
        FROM
          transactions t
        JOIN contracts c ON t.contractId = c.id
        JOIN insertions i ON c.id = i.contractId
        JOIN magazines m ON i.magazineId = m.id
        WHERE m.id = 26
          AND t.isChargedBack IS FALSE
          AND t.`timestamp` >= '2013-09-12'
          AND t.`timestamp` <= date_add('2013-09-12',interval 1 month)
        GROUP BY 
          t.id;

And this is my result:

39600
9600
61500
9000
43200
49680
14220
5925
7020
36000
72900

????

Upvotes: 0

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You have a problem with your original query. It is not doing what you think it is doing. You are aggregating by t.id, but selecting t.amount. That means that MySQL is choosing an arbitrary value.

Why is this happening? One possibility is that the following is the right amount for each transaction:

    SELECT sum(t.amount)
    FROM transactions t
    JOIN contracts c ON t.contractId = c.id
    JOIN insertions i ON c.id = i.contractId
    JOIN magazines m ON i.magazineId = m.id
    WHERE m.id = 26
      AND t.isChargedBack IS FALSE
      AND t.`timestamp` >= '2013-09-12'
      AND t.`timestamp` <= date_add('2013-09-12',interval 1 month)
    GROUP BY t.id;

If so, just removing the group by will give the right total.

Another possibility is that the joins are multiplying the rows. My suspicion would be insertions. If that is the case, then t.amount is being repeated and the query you want would choose just one value:

    SELECT min(t.amount)
    FROM transactions t
    JOIN contracts c ON t.contractId = c.id
    JOIN insertions i ON c.id = i.contractId
    JOIN magazines m ON i.magazineId = m.id
    WHERE m.id = 26
      AND t.isChargedBack IS FALSE
      AND t.`timestamp` >= '2013-09-12'
      AND t.`timestamp` <= date_add('2013-09-12',interval 1 month)
    GROUP BY t.id;

If this is the problem, then to get the full sum, you can use a sub-query:

select sum(amount)
from (SELECT min(t.amount) as amount
      FROM transactions t
      JOIN contracts c ON t.contractId = c.id
      JOIN insertions i ON c.id = i.contractId
      JOIN magazines m ON i.magazineId = m.id
      WHERE m.id = 26
        AND t.isChargedBack IS FALSE
        AND t.`timestamp` >= '2013-09-12'
        AND t.`timestamp` <= date_add('2013-09-12',interval 1 month)
      GROUP BY t.id
     ) t

Upvotes: 1

Related Questions