php - 选择所有值并对不同表中的字段值同时求和

Question

我有以下查询，没有 ,SUM(amount) AS total效果很好。现在的问题是我想从我的表vouchers中选择所有值同时想要获取amount字段的总和值来 self 的另一张名为 details 的表.

SELECT *,SUM(amount) AS total FROM vouchers 
LEFT JOIN details on vouchers.voucher_no = details.voucher_no 
 LEFT JOIN   accounts on accounts.code = vouchers.account_code 
 WHERE (voucher_type='1' AND t_code=$code)

我尝试了上述查询，但它显示了以下错误。(请检查链接)

http://i44.tinypic.com/bdaq.png

Answer 1

将details总和放入子查询中，您LEFT JOIN。您的原始版本缺少 SUM() 聚合的任何 GROUP BY 子句。

SELECT
  vouchers.*,
  /* from the subquery... */
  detailssum.total
FROM
  vouchers
  LEFT JOIN (
    /* Subquery gets total per voucher_no to join against vouchers */
    SELECT voucher_no, t_code, SUM(amount) as total FROM details GROUP BY voucher_no
  ) detailssum ON vouchers.voucher_no = detailssum.voucher_no
  LEFT JOIN accounts on accounts.code = vouchers.account_code 
WHERE (voucher_type='1' AND t_code=$code)

(我们假设 $code 已包含经过清理和边界检查的值)