MySQL的取和PHP [英] mysql fetch sum php

问题描述

sql列-trans_value同时包含正值和负值。

所以我试图弄清楚如何设置正值和负值的总和放在一边，这样我就可以计算出正数和负数的总和。

最后，我可以同时减去正数-负数。

编辑，
i在此之前忘记提及

在同一列中也有列名称产品ID表，所以

  product_id | trans_value 
 1200 
 1250 
 1 -150 
 2500 
 2 -300 
 3200 
 3 -150 
  

所以我需要获取产品ID 1的总和，将其显示出来，然后输入2、3，依此类推。 / p>

谢谢

解决方案

如果您只想查看每个项目的总数product_id

  SELECT product_id，SUM（trans_value）
从表
 GROUP BY product_id 
 ORDER BY product_id 
  

如果您确实需要分别使用正值和负值：

 选择SUM（IF（trans_value< 0; trans_value; 0））neg，SUM（IF（trans_value> 0; trans_value; 0））pos 
 FROM表
  

将负值的总和设为 neg ， pos 中的正值之和。 pos + neg 将是总金额。

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

解决方案

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.

这篇关于MySQL的取和PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！