CODEWITHSUNDEEP
phpmysql
black_belt
black_belt

Reputation: 6799

Selecting all values and sum up a field's value from different table at the same time

I have the following query, without the ,SUM(amount) AS total it works fine. Now the problem is I want to select all the values from my table vouchers and at the same time want to get the sum value of the field amount from my another table called 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)

I tried the above query but it showed me the following error.(pls check the link)

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

Upvotes: 0

Views: 198

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

Place the details sum into a subquery, against which you LEFT JOIN. Your original was missing any GROUP BY clause for your SUM() aggregate.

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)

(We assume $code contains a sanitized and bounds-checked value already)

Upvotes: 1

Related Questions