php - LEFT JOIN 与 SUM 不工作

Question

希望我能解决我遇到的 LEFT JOIN + SUM 问题。

背景:我正在构建一个小型财务系统，并希望计算给定月份(空白月份 = 空)内所有发票的值(value)。我有两个表:

tsm_finance_calendar - 包含“月份”。tsm_finance_invoices - 包含每张发票的详细信息。

我的查询:

<?php
$query = "SELECT tsm_finance_calendar.month,
                 SUM(tsm_finance_invoices.totalBilled)
          FROM tsm_finance_calendar
          LEFT JOIN tsm_finance_invoices
              ON tsm_finance_calendar.month = tsm_finance_invoices.month
          GROUP BY tsm_finance_calendar.month
          ORDER BY 'id'"; 
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo $row['month']. " - $". $row['SUM(totalBilled'];
echo "<br />";
}
?>

输出在正确的轨道上(月份 - $空白)但缺少求和结果。

任何帮助都会得到巨大的击掌:)

谢谢，RR

Answer 1

在查询中使用as关键字

$query = "SELECT tsm_finance_calendar.month, SUM(tsm_finance_invoices.totalBilled) as sum FROM tsm_finance_calendar LEFT JOIN tsm_finance_invoices ON tsm_finance_calendar.month = tsm_finance_invoices.month GROUP BY tsm_finance_calendar.month ORDER BY 'id'"; 
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo $row['month']. " - $". $row['sum'];
echo "<br />";
}