CODEWITHSUNDEEP
mysql
John
John

Reputation: 952

MYSQL calculation is returning a wrong value

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

Upvotes: 0

Views: 296

Answers (1)

Shadow
Shadow

Reputation: 34231

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.

Upvotes: 2

Related Questions