MySQL, how can I reference a subquery field in a SELECT query

Question

I am trying to calculate account balances in MySQL using the following query

SELECT accountNumber,
(
    SELECT  amount
    FROM    accountDebits
    WHERE   accountNumber = accounts.accountNumber
) debits,
(
    SELECT  amount
    FROM    accountCredits 
    WHERE   accountNumber = accounts.accountNumber
) credits,
credits - debits as balance
FROM    accounts

But I get this error:

Error Code: 1054. Unknown column 'credits' in 'field list'

How can I refer to the subquery aliases in the select?

Answer 1

Wrap it with another subquery that does the calculation:

SELECT accountNumber, debits, credits, credits - debits as balance
FROM (
  SELECT accountNumber,
  (
    SELECT  amount
    FROM    accountDebits
    WHERE   accountNumber = accounts.accountNumber
  ) debits,
  (
    SELECT  amount
    FROM    accountCredits 
    WHERE   accountNumber = accounts.accountNumber
  ) credits
  FROM accounts
  ) x

While this gets your query up and running, you'd be better off with an outer join:

SELECT accountNumber, debits, credits, credits - debits as balance
FROM (
  SELECT
      a.accountNumber,
      coalesce(sum(d.amount), 0) debits,
      coalesce(sum(c.amount), 0) credits,
  FROM accounts a
  LEFT JOIN accountDebits d
    ON d.accountNumber = a.accountNumber
  LEFT JOIN accountCredits c
    ON c.accountNumber = a.accountNumber
) x