mysql - Error when trying to use aliased column name in where clause -

i have problem sql query. want select records debt amount. amount counted relation 2 tables.

this works fine:

select `i`.*,  (i.amount_netto + (i.amount_netto * i.vat / 100)) - (sum(p.amount_netto)) `debt`, `e`.`name` `user_name`, `e`.`surname` `user_surname`, `c`.`name` `contractor_name` `invoices` `i`  inner join `payments` `p` on i.id = p.invoice_id  inner join `employees` `e` on i.employee_id = e.id  inner join `contractors` `c` on i.contractor_id = c.id  group `i`.`id`  order `debt` asc 

but when add clause debt error:

unknown column 'debt' in 'where clause' 

query looks this:

select `i`.*,  (i.amount_netto + (i.amount_netto * i.vat / 100)) - (sum(p.amount_netto)) `debt`,  `e`.`name` `user_name`, `e`.`surname` `user_surname`, `c`.`name` `contractor_name` `invoices` `i`  inner join `payments` `p` on i.id = p.invoice_id  inner join `employees` `e` on i.employee_id = e.id  inner join `contractors` `c` on i.contractor_id = c.id  `debt` > 1  group `i`.`id`  order `debt` asc 

why can't access debt in clause, can in order clause?

debt not column, alias. column (expression) aliases not resolved until after query executed, therefore cannot used in clauses.

for example query not legal:

select foo + 3 bar baz bar = 39 

and instead have rewrite whole expression in clause:

select foo + 3 bar baz foo + 3 = 39 

---

furthermore, since debt based on aggregate, cannot filter in clause. must instead use having evaluate predicate after aggregation. therefore query should be:

select ... ... group ... having (i.amount_netto + (i.amount_netto * i.vat / 100)) - (sum(p.amount_netto)) > 1 order ... 

note mysql offers sql extension enables use of aliases in having clause, can do:

select ... ... group ... having debt > 1 order ...