我使用以下代码返回购买详细信息。显示的结果如下所示
Name Qty Value
Product1 30 1000
Product1 45 2000
Product2 20 1500
Product2 25 3000
如何汇总 pdt 的数量和值并像这样显示
Name Qty Value
Product1 75 3000
Product2 45 4500
Total 120 7500
使用"选择位置"子句。
$result = mysql_query("SELECT pdtname,pack,qty,value FROM ist WHERE chemistcode='3121' AND chemistcode='3020' AND companycode = $companycode");
echo "<table id=customers>
<tr>
<th>Product</th>
<th>Pack</th>
<th>Qunatity</th>
<th>Value</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo"<td>" . $row['pdtname'] . "</td>";
echo"<td>" . $row['pack'] . "</td>";
echo"<td>" . $row['qty'] . "</td>";
echo"<td>" . $row['value'] . "</td>";
echo "</tr>";
}
echo "</table>";
您可以使用
聚合函数sum()
和使用group by with rollup
轻松获得结果:
select
coalesce(name, 'Total') Name,
sum(qty) qty,
sum(value) value
from ist
group by name with rollup;
请参阅 SQL 摆弄演示。这给出了结果:
| NAME | QTY | VALUE |
| Product1 | 75 | 3000 |
| Product2 | 45 | 4500 |
| Total | 120 | 7500 |
select name,sum(Qty),sum(value) from table2
group by name
union
select 'total',sum(Qty),sum(value) from table2