CODEWITHSUNDEEP
mysql
EnzoTrompeneers
EnzoTrompeneers

Reputation: 1091

Sum negative integer gives wrong result

My SUM with condition that has negative integer makes a wrong calculation.

If CN (creditnote) is true then total of that row must be set negative and then SUM the totals together.

+----+-------+------------+-------+
| id | CN    | date       | total |
+----+-------+------------+-------+
| 1  | false | 2019-01-01 | 30    |
+----+-------+------------+-------+
| 2  | true  | 2019-01-01 | 15    |
+----+-------+------------+-------+

SELECT 
  MONTH(invoices.date) as month,
  IF(invoices.CN = true,
    CAST(SUM(DISTINCT COALESCE(invoices.total * -1, 0)) AS SIGNED),
    CAST(SUM(DISTINCT COALESCE(invoices.total, 0)) AS SIGNED)) AS total,
  from invoices
  GROUP BY month

Result

+-------+------+
| month | total|
+-------+------+
| 1     | -30  |
+-------+------+

What I expect: 30 + (-15) = 15

+-------+------+
| month | total|
+-------+------+
| 1     |  15  |
+-------+------+

Upvotes: 0

Views: 73

Answers (2)

Strawberry
Strawberry

Reputation: 33935

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (cn TINYINT, total INT);

INSERT INTO my_table VALUES (false,30),(true,15);    

SELECT SUM((0.5-cn)*2*total)x FROM my_table;
+------+
| x    |
+------+
| 15.0 |
+------+
1 row in set (0.00 sec)

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

This is on track for what you have in mind:

SELECT
    MONTH(date) AS month,
    SUM(CASE WHEN CN = 'true' THEN -1.0 * total ELSE total END) AS total
FROM invoices
GROUP BY
    MONTH(date);

I say this is close, because it is not clear how or why CN is ending up in the aggregated result. It makes sense to me, to report only the month and the total.

Upvotes: 4

Related Questions