Arithmetic Calculation based on previous MySQL queries

Question

I have a DB with the table 'account' which has various types of transactions. I succesfully carried out the query to find "Total Credit" and "Total Debit". I need help to Calculate the Balance .i.e., If "Total Credit > Total Debit" answer should be "Balance Cr." else if "Total Credit < Total Debit" answer should be "Balance Dr." else answer should be "0"

<?php
    $pp= ("SELECT SUM(amount) FROM account WHERE (mode='cash' AND type='payment') OR (mode='cash' AND type='purchase')");
    $total_pp = mysql_query($pp);
    while($gtotal_pp = mysql_fetch_array($total_pp)){
        echo "<table><tr><td>Total Credit</td><td>:</td><td>$</td><td align='right'>" . $gtotal_pp['SUM(amount)'] . "</td></tr>";
        echo "<br/>";
    }

    $sr = ("SELECT SUM(amount) FROM account WHERE (mode='cash' AND type='sale') OR (mode='cash' AND type='reciept')");
    $total_sr = mysql_query($sr);
    while($gtotal_sr = mysql_fetch_array($total_sr)){
        echo "<tr><td>Total Debit</td><td>:</td><td>$</td><td align='right'>" . $gtotal_sr['SUM(amount)'] . "</td></tr>";
    }

    $bgtotal_pp = $gtotal_pp['SUM(amount)'];
    $bgtotal_sr = $gtotal_sr['SUM(amount)'];
    $balance_cr = $bgtotal_pp - $bgtotal_sr;
    $balance_dr = $bgtotal_sr - $bgtotal_pp;

    if ($bgtotal_pp > $bgtotal_sr)
        echo "<tr><td>Balance (Cr.)</td><td>:</td><td>$</td><td align='right'>" . $balance_cr . "</td></tr></table>";

    else if ($bgtotal_pp < $bgtotal_sr)
        echo "<tr><td>Balance (Dr.)</td><td>:</td><td>$</td><td align='right'>" . $balance_dr . "</td></tr></table>";

    else
        echo"<tr><td>Balance</td><td>:</td><td></td><td align='right'>0.00</td></tr></table">;

    ?>

When

$bgtotal_pp = "(some number)";
$bgtotal_sr = "(some number");

the code works perfectly. It is only when I try to bring the previous query instead of some number, it always shows the "Else statement".

Thanks in advance.

Gordon Linoff · Accepted Answer · 2014-11-09 12:36:15Z

1

This is in regards to the queries, not the php code (with such issues as using mysql_). Why are you doing multiple queries and then arithmetic in the application. You can do all this with one query:

SELECT SUM(CASE WHEN mode = 'cash' and type in ('payment', 'purchase') then amount end) as Credit,
       SUM(CASE WHEN mode = 'cash' and type in ('sale', 'receipt') then amount end) as Debit,
       SUM(CASE WHEN mode = 'cash' and type in ('payment', 'purchase') then amount
                WHEN mode = 'cash' and type in ('sale', 'receipt') then - amount
                ELSE 0
           END) as Balance       
FROM account;

Normally, I would expect a where clause or group by to get the information for specific accounts, but that is not in your original data.

answered Nov 9, 2014 at 12:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ahamed Zulfan Over a year ago

I'm just a beginner in PHP. I've changed my query as the one above. How is that i print the results?

Elin Over a year ago

Did you change the rest of the code to reflect the changes in the query? Also there is no reason to save the value of an array element into another variable here, just do comparisons directly on the elements, but @gordon-linoff has already calculated the balance for you so you don't need to do that. Also I would think you would want an ELSE 0 for the first two cases as well as the third.

Gordon Linoff Over a year ago

@Elin . . . I agree with the else 0 in principle. However, this version mimics the original logic.

Ahamed Zulfan Over a year ago

I haven't changed rest of the code yet. Can you help me with it? I just found a few codes from various websites to conclude at the above code. I'm just a amateur in PHP & MySQL. I've been learning everything from the WEB.

Collectives™ on Stack Overflow

Arithmetic Calculation based on previous MySQL queries

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related