Paul John Diwa Paul John Diwa - 5 months ago 9
MySQL Question

Getting unknown column error , where-ing a column from a subquery result

SELECT AID,DESCRIPTION,
IFNULL((SELECT SUM(DEBIT) FROM `GL_POSTED_TRANSACTION` WHERE AID = account.AID),0) AS PREV_DEBIT,
IFNULL((SELECT SUM(DEBIT) FROM `GL_POSTED_TRANSACTION` WHERE AID = account.AID),0) AS DEBIT
FROM GL_ACCOUNT account
WHERE CLASSID = 1 AND SUBCLASSID = 3
ORDER BY DESCRIPTION;


The query above results:
Result

but when I add AND DEBIT > 0 I got an error saying unknown column DEBIT.

What am I doing wrong? can somebody explain it.

Answer

Add that condition in HAVING clause, not WHERE, like;)

SELECT AID,DESCRIPTION,
IFNULL((SELECT SUM(DEBIT) FROM `GL_POSTED_TRANSACTION` WHERE AID = account.AID),0) AS PREV_DEBIT,
IFNULL((SELECT SUM(DEBIT) FROM `GL_POSTED_TRANSACTION` WHERE AID = account.AID),0) AS DEBIT 
FROM GL_ACCOUNT account 
WHERE CLASSID = 1 AND SUBCLASSID = 3
HAVING DEBIT > 0
ORDER BY DESCRIPTION;