php - 如何获取不同表中两个字段的总和？

Question

如何获取Purchase_Order.QTY和Purchase_Request.QTY的总和作为余额？我的问题是Purchase_Order 表中有多个po_number 具有相同的计数器。

下面是我的表格，

我需要获取purchase_order中100001号柜台的总数量，这样我就可以得到QTYpurchase_order和QTYpurchase_request之间的差异

表Purchase_Order

counter | qty |

100001  | 10  |
100001  | 10  |
100001  | 10  |
100004  | 30  |

表格购买请求

counter | total_qty |

100001  |     50    |
100002  |     100   |
100003  |     50    |
100004  |     70    |

这是我的示例输出

输出

counter | total_qty | balance |

100001  |     50    |  20     |
100002  |     100   |  100    |  
100003  |     50    |  50     |
100004  |     70    |  40     |

这是我的脚本，

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

    $result = $mysqli->query("

    ");
    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>";

?>

帮忙？

Answer 1

SELECT PR.Counter,pr.qty-po.qty as remainingqty FROM Purchase_Request pr LEFT JOIN (Select counter,sum(qty) from Purchase_Order group by counter)po ON pr.Counter=po.Counter