Get the difference of two column in two different tables?

Question

My sql codes execute but I don't know how get the difference of purchase_request total_qty and purchase_order qty.

Table Purchase_Order

counter | qty |      
---------------         
100001  | 10  |  
100001  | 10  |  
100001  | 10  |  
100004  | 30  |

Table Purchase_Request

counter | total_qty |
---------------------
100001  |     50    |  
100002  |     100   |  
100003  |     50    |  
100004  |     70    |

I want to code just like this, but I don't know how to mix it in my codes.

a.total_qty-b.qty as balance

And this is my codes

<?php
    $mysqli = new mysqli("localhost", "root", "", "test");

        $result = $mysqli->query("
        select a.counter,a.total_qty from purchase_request a inner join purchase_order b on a.counter= b.counter group by a.counter
        ");
        echo'<table id="tfhover" cellspacing="0" class="tablesorter" style="text-transform:uppercase;" border="1px">
            <thead>
            <tr>
            <th></th>
        <th>counter</th>
        <th>QTY</th>
        <th>balance</th>
            </tr>
            </thead>';
            echo'<tbody>';
        $i=1;   
    while($row = $result->fetch_assoc()){
        echo'<tr>
                <td>'.$i++.'</td>
                <td>'.$row['counter'].'</td>
                <td>'.$row['total_qty'].'</td>
                <td>'.$row['balance'].'</td>
            </tr>';
           }
        echo "</tbody></table>";

    ?>

mucio · Accepted Answer · 2013-11-14T11:20:10.663

0

Did you try this?

    select a.counter,
           a.total_qty,
           a.total_qty - b.qty balance 
      from (select counter,
                   sum(total_qty) total_qty
              form purchase_request
          group by counter) a 
inner join (select counter,
                   sum(qty) qty
              from purchase_order 
          group by counter) b 
        on a.counter= b.counter 
  group by a.counter

Edit: I got it, what you need is to aggregate your quantities and then do the math

edited Nov 14 '13 at 11:20

answered Nov 14 '13 at 10:34

mucio

7,014
1
21
33

it works but the balance is 40, I need to get the sum of 3 100001 counter of qty and minus to total_qty. I should get 20 as balance – user2991590 Nov 14 '13 at 10:38
it's getting worst, the $row['total_qty'] got lost, and the balance of 100001 becomes 120 :/ – user2991590 Nov 14 '13 at 10:43
I got it, I changed it, a.total_qty, a.total_qty - sum(b.qty) balance. Just one thing, why it's working without ( as ) ? Sorry I'm new in sql – user2991590 Nov 14 '13 at 10:48
I changed again the query, I think I that should be what you are looking for – mucio Nov 14 '13 at 11:01
Fatal error: Call to a member function fetch_assoc() on a non-object – user2991590 Nov 14 '13 at 11:14
there was a ) that I forgot to remove after the second a.total_qty – mucio Nov 14 '13 at 11:21
I prefer without `AS` but not everybody likes this way – mucio Nov 14 '13 at 12:16
Ok thanks :)) I'm creating another question with this code, would you mine to check it? Joining 3 tables connected by counter. – user2991590 Nov 14 '13 at 12:48

score -1 · Answer 2 · edited Nov 14 '13 at 12:20

-1

    select a.counter,
           a.total_qty,
           sum(a.total_qty) - sum(b.qty) as balance 
      from purchase_request a 
left inner join purchase_order b 
        on a.counter= b.counter 
  group by a.counter

edited Nov 14 '13 at 12:20

mucio

7,014
1
21
33

answered Nov 14 '13 at 11:01

KanimozhiEthiraj

76
1
1

the counter 100001 balance = 120 which is wrong, but the counter 100004 = 40 which is correct. Why is that? but this is my answer a.total_qty - sum(b.qty) as balance and both counter = is correct. If it is right? – user2991590 Nov 14 '13 at 11:17
this will give you cartesian join between Purchase_Order and purchase_request when there are multiple row with the same counter – mucio Nov 14 '13 at 12:15

Get the difference of two column in two different tables?

2 Answers2