douyan1927
2017-02-08 23:54
采纳率: 0%
浏览 1.2k
已采纳

MYSQL SUM的正负值

I have a column name 'AmountLC' in the db table. Another column name 'DebitCredit' determines whether the 'AmountLC' is Positive or Negative. If DebitCredit="H", the values in AmountLC is Negative. If DebitCredit="S", the values in AmountLC is Positive. I need to make query to get the sum of AmountLC. I have tried to get the logic but didn't get any. I would be grateful if anyone can help me.

I tried the below code as per other stackoverflow discussions.

$F7 = mysql_query("SELECT 
                SUM(CASE WHEN DebitCredit='H' THEN DebitCredit ELSE 0 END) as NegativeTotal,
                SUM(CASE WHEN DebitCredit='S' THEN DebitCredit ELSE 0 END) as PostiveTotal 
                FROM T1_CSV_Table 
                WHERE Month='$getMonth' AND TaxCode='A0'");

THANKS! Update Code

$F7 = mysql_query("SELECT 
                    SUM(CASE WHEN DebitCredit='H' THEN AmountLC*-1 ELSE AmountLC END) as Total 
                    FROM T1_CSV_Table 
                    WHERE Month='$getMonth' AND TaxCode='A0'");
    $rowf7 = mysql_fetch_array($F7);
    echo "Total-".$rowf7['Total'];

Update code for results: SELECT AmountLC,DebitCredit FROM T1_CSV_Table WHERE TaxCode="A0" AND Month="12"

enter image description here

图片转代码服务由CSDN问答提供 功能建议

我在db表中有一个列名'AmountLC'。 另一个列名“DebitCredit”确定'AmountLC'是正还是负。 如果DebitCredit =“H”,则AmountLC中的值为负。 如果DebitCredit =“S”,则AmountLC中的值为Positive。 我需要进行查询以获得AmountLC的总和。 我试图得到逻辑,但没有得到任何。 如果有人能帮助我,我将不胜感激。

我按照其他stackoverflow讨论尝试了以下代码。

  $ F7 = mysql_query  (“SELECT 
 SUM(如果DebitCredit ='H'那么DebitCredit ELSE 0 END)作为NegativeTotal,
 SUM(CASE WHEN DebitCredit ='S'THEN DebitCredit ELSE 0 END)为PostiveTotal 
 FROM T1_CSV_Table 
 WHERE Month  ='$ getMonth'AND TaxCode ='A0'“); 
   
 
 

感谢! 更新代码

   $ F7 = mysql_query(“SELECT 
 SUM(CASE WHEN DebitCredit ='H'THEN AmountLC * -1 ELSE AmountLC END)as Total 
 FROM T1_CSV_Table 
 WHERE Month ='$ getMonth'AND TaxCode ='A0'”)  ; 
 $ rowf7 = mysql_fetch_array($ F7); 
 echo“Total  - ”。$ rowf7 ['Total']; 
   
 
 

更新结果代码: SELECT AmountLC DebitCredit FROM T1_CSV_Table WHERE TaxCode =“A0”AND Month =“12 “ \ n

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doulu6314 2017-02-09 00:27
    已采纳

    You can do that calculation all in one go if all you want is a total of the debits and credits

    SELECT 
        SUM(CASE WHEN DebitCredit='H' THEN AmountLC*-1 ELSE AmountLC END) as Total
    FROM T1_CSV_Table 
    WHERE Month='$getMonth' AND TaxCode='A0'
    

    NOTE: Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared parameterized statements

    已采纳该答案
    打赏 评论
  • dpb75177 2017-02-09 00:02

    try this please:

    SELECT
        SUM(IF(DebitCredit='H', DebitCredit, 0) AS NegativeTotal,
        SUM(IF(DebitCredit='S', DebitCredit, 0) AS PostiveTotal
    FROM T1_CSV_Table 
    WHERE
        MONTH='$getMonth'
    AND
        TaxCode='A0';
    
    打赏 评论

相关推荐 更多相似问题