-2
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.

Paul John Diwa
  • 319
  • 1
  • 9

1 Answers1

1

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;
Blank
  • 12,158
  • 1
  • 12
  • 30
  • Or you could just put the explicit condition in the `WHERE` clause. This won't work in the case of a query which has a `GROUP BY`, in which case `HAVING` has a different application. – Tim Biegeleisen Jun 27 '16 at 04:26