I have a mysql query that subtracts amounts from several rows to show a new amount in a new column called "Balance". I would like it to only show those records where Balance > '0'. See below:
SELECT
visits.visit_id,
visits.payment_increments,
((visits.total - visits.paid) - SUM(payments.amount)) AS Balance,
schedule.schedule_date
FROM
visits
LEFT OUTER JOIN
schedule ON visits.schedule_id = schedule.id
LEFT OUTER JOIN
payments ON visits.visit_id = payments.visit_id
WHERE
Balance>'0'
GROUP BY visits.visit_id
Is there a way to use my new column in the WHERE statement? Right now, all it shows is an error -> Error Code:1054 Unknown column 'Balance' in 'where clause'.
Any help is much appreciated!
use HAVING instead of WHERE
GROUP BY ... HAVING Balance >0
Note : HAVING works works with non-table columns ..Since Balance is not a column of main table, so this is an imaginary column, so it will work with HAVING just like WHERE works with table columns