mysql - 列的总和并显示多个查询

Question

我有下表:MyTable

我需要显示以下数据:生产线购买价格建议零售价

buyPrice 和 MSRP 都需要对其数据使用 SUM 并按 ProductLine 进行索引，因此输出将如下所示:

产品线

飞机
轮船
火车
卡车和船只

购买价格
每个产品线的 buyPrice 中所有值的总和

建议零售价
每个产品线建议零售价中所有值的总和

我使用以下查询但没有成功:

$result = mysql_query("SELECT SUM(buyPrice) FROM myTable, SELECT SUM(MSRP) FROM myTable, SELECT * FROM myTable"); // selecting data through mysql_query()

这是我的条件

while($row = mysql_fetch_array($result))
{
// we are running a while loop to print all the rows in a table
echo "<tr>";  
echo "<td align='center' width='200'>" . $row['productLine'] . "</td>";  
echo "</tr>";  

echo "<tr>";  
echo "<td align='center' width='200'>" . $row['SUM(buyPrice)'] . "</td>";  
echo "</tr>"; 
}  

echo "<tr>";  
echo "<td align='center' width='200'>" . $row['SUM(MSRP)'] . "</td>";  
echo "</tr>"; 
echo "</table>";  

我没有得到任何输出。非常感谢任何帮助。

Answer 1

尝试使用:

$result = mysql_query("SELECT productLine, SUM(buyPrice) AS sum_buy_price, SUM(MSRP) AS sum_msrp FROM myTable group by productLine"); // selecting data through mysql_query()

并输出结果:

echo "<table>";
while($row = mysql_fetch_array($result))
{
    // we are running a while loop to print all the rows in a table
    echo "<tr>";  
    echo "<td align='center' width='200'>" . $row['productLine'] . "</td>";  
    echo "<td align='center' width='200'>" . $row['sum_buy_price'] . "</td>";  
    echo "<td align='center' width='200'>" . $row['sum_msrp'] . "</td>";  
    echo "</tr>"; 
}
echo "</table>";