mysql fetch sum php

Question

sql column - trans_value contain both positive and negative value amount.

so i'm trying to figure out how do i set a sum of positive value and negative value aside, so that i can calculate the how much sum of positive and how much is sum of negative.

so in the end, i can minus both, positive - negative.

edit, i forgot to mention before that

there is also column name product id in same table, so

product_id | trans_value
1               200
1               250
1               -150
2               500
2               -300
3               200
3               -150

so i need to get sum of product id 1, display it out, then for 2, 3 and so on.

thanks

Konerak · Accepted Answer · 2010-07-09T06:21:39.140

3

If you just want to see the total for each product_id

SELECT product_id, SUM(trans_value)
FROM table
GROUP BY product_id
ORDER BY product_id

If you really need the positive and negative values seperately:

SELECT SUM(IF(trans_value<0;trans_value;0)) neg, SUM(IF(trans_value>0;trans_value;0)) pos
FROM table

Will put the sum of the negative values in neg, the sum of the positive values in pos. pos + neg will be the total sum.

edited Jul 09 '10 at 06:21

answered Jul 08 '10 at 15:57

Konerak

39,272
12
98
118

hi sorry, i forgot to mention earlier before, i edited my post, check pls thanks – damien Jul 08 '10 at 16:05
is it possible to come up with php method? – damien Jul 08 '10 at 16:17
uh, `pos - neg` is not the total sum, the total sum is `pos + neg`. – Mark Elliot Jul 09 '10 at 05:56

score 0 · Answer 2 · answered Jul 08 '10 at 15:58

0

SELECT SUM( trans_value ) AS sum FROM table WHERE trans_value > 0;

answered Jul 08 '10 at 15:58

eklundchristopher

159
7

That will only sum the positives. OP asked for the negatives too. – Konerak Jul 08 '10 at 16:01

mysql fetch sum php

2 Answers2

Linked