MYSQL SUM of positive and negative values

Question

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"

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Feb 08 '17 at 23:57
How about `SUM(CASE WHEN DebitCredit='H' THEN AmountLC*-1 ELSE AmountLC END) as Total,` — RiggsFolly, Feb 09 '17 at 00:01
yeah, i know it's not a logic. just tried a code. There are no separate positive and negative values. I need to decide depends on the DebitCredi value. — Zendie, Feb 09 '17 at 00:01
@RiggsFolly I have updated the code as you said. But nothing returns — Zendie, Feb 09 '17 at 00:13
When you say nothing is returned what do you mean, if the query runs you should get something back even if its not the right number — RiggsFolly, Feb 09 '17 at 00:21
Total-array(2) { [0]=> NULL ["Total"]=> NULL } This is the array I get when I run the query — Zendie, Feb 09 '17 at 00:23
Are you sure you have any values in the DB for this month and TaxCode — RiggsFolly, Feb 09 '17 at 00:26
Do a test for me, run `SELECT AmountLC, DebitCredit WHERE Month='Hard_code_a_valid_month' AND TaxCode='A0'` and show us a few results — RiggsFolly, Feb 09 '17 at 00:30

score 1 · Accepted Answer · edited May 23 '17 at 12:24

1

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

edited May 23 '17 at 12:24

Community

1
1

answered Feb 09 '17 at 00:27

RiggsFolly

93,638
21
103
149

I am accepting this an answer. I will try to figure it out whats wrong with my code. Thanks for the logic :) – Zendie Feb 09 '17 at 00:31

score -1 · Answer 2 · answered Feb 09 '17 at 00:02

-1

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';

answered Feb 09 '17 at 00:02

Bernd Buffen

14,525
2
24
39

I didn't. I am trying this. Thanks – Zendie Feb 09 '17 at 00:04
Why bother, its not easy adding and substracting `H`'s and `S`'s and getting any sort of useful answer – RiggsFolly Feb 09 '17 at 00:06
It mus not. Look at the question again – RiggsFolly Feb 09 '17 at 00:11

MYSQL SUM of positive and negative values

2 Answers2