MYSQL calculation is returning a wrong value

Question

I have two columns in my database. Column r and column l. I have two rows in it. Row 302 and row 303.

For row 302 I have the following data

---------------------------
|   k_id   |   l   |   r  |
|--------------------------
| 302      | 400   | 0    |
| 303      | 0     | 400  |
---------------------------

I am trying to run an SQL statement to select the total in row l and l.

In this case the calculation must be: 400 - 400 = 0. But when I run the query I get 400 instead of 0.

Does someone know why I dont get the right response?

Here is my sql statement:

SELECT COALESCE(SUM(l), 0) - COALESCE(SUM(r), 0) as total
FROM trans
WHERE user_id = '1' AND k_id IN ('302', '303')
GROUP BY k_id WITH ROLLUP LIMIT 0,1

Why use COALESCE. Why not just add up "l" and then subtract "r" from it? — Difster
– Difster, Commented Jul 9, 2017 at 0:06
I deleted the limit, Without the limit the response is three rows, First row: 400, second row: -400, third row: 0 — John
– John, Commented Jul 9, 2017 at 0:08
The group by is the problem, you create multiple groups instead of one. Try group by user_id — Matzi
– Matzi, Commented Jul 9, 2017 at 0:11

Shadow · Accepted Answer · 2017-07-09 00:10:55Z

2

The problem is the group by k_id part of the statement. This instructs mysql to do the sum by k_id values, therefore 302 and 303 will be summed up separately. Remove the group by clause and the rollup and you will get 0.

answered Jul 9, 2017 at 0:10

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL calculation is returning a wrong value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related