CODEWITHSUNDEEP
mysql
dtx
dtx

Reputation: 340

Error when trying to use aliased column name in where clause

I have a problem with my SQL query. I want to select records by debt amount. Amount is counted by relation with two tables.

This works fine:

SELECT `i`.*, 
(i.amount_netto + (i.amount_netto * i.vat / 100)) - (SUM(p.amount_netto)) AS `debt`,
`e`.`name` AS `user_name`,
`e`.`surname` AS `user_surname`,
`c`.`name` AS `contractor_name`
FROM `invoices` AS `i` 
INNER JOIN `payments` AS `p` ON i.id = p.invoice_id 
INNER JOIN `employees` AS `e` ON i.employee_id = e.id 
INNER JOIN `contractors` AS `c` ON i.contractor_id = c.id 
GROUP BY `i`.`id` 
ORDER BY `debt` ASC

But when I add a WHERE clause by debt I get an error:

Unknown column 'debt' in 'where clause'

Query looks like this:

SELECT `i`.*, 
(i.amount_netto + (i.amount_netto * i.vat / 100)) - (SUM(p.amount_netto)) AS `debt`, 
`e`.`name` AS `user_name`,
`e`.`surname` AS `user_surname`,
`c`.`name` AS `contractor_name`
FROM `invoices` AS `i` 
INNER JOIN `payments` AS `p` ON i.id = p.invoice_id 
INNER JOIN `employees` AS `e` ON i.employee_id = e.id 
INNER JOIN `contractors` AS `c` ON i.contractor_id = c.id 
WHERE `debt` > 1 
GROUP BY `i`.`id` 
ORDER BY `debt` ASC

Why I can't access debt in the WHERE clause, but I can in the ORDER clause?

Upvotes: 2

Views: 74

Answers (2)

lc.
lc.

Reputation: 116458

debt is not a column, but an alias. Column (expression) aliases are not resolved until after the query is executed, therefore they cannot be used in WHERE clauses.

For example this query is not legal:

select foo + 3 as bar
from baz
where bar = 39

And instead you have to rewrite the whole expression in the WHERE clause:

select foo + 3 as bar
from baz
where foo + 3 = 39

Furthermore, since debt is actually based on an aggregate, you cannot filter this in a WHERE clause. You must instead use HAVING to evaluate the predicate after the aggregation. Therefore your query should be:

SELECT ...
FROM ...
GROUP BY ...
HAVING (i.amount_netto + (i.amount_netto * i.vat / 100)) - (SUM(p.amount_netto)) > 1
ORDER BY ...

Note that MySql offers an SQL extension which enables the use of aliases in the HAVING clause, so you can also do:

SELECT ...
FROM ...
GROUP BY ...
HAVING debt > 1
ORDER BY ...

Upvotes: 3

Fedri Qrueger
Fedri Qrueger

Reputation: 641

SQL doesn't typically allow you to reference column aliases in WHERE, GROUP BY or HAVING clauses. MySQL does support referencing column aliases in the GROUP BY and HAVING

try change your where query to 

WHERE (i.amount_netto + (i.amount_netto * i.vat / 100)) - (SUM(p.amount_netto)) > 1 GROUP BY i.id ORDER BY debt ASC

another option, you could use a subquery

SELECT * from (`i`.*, (i.amount_netto + (i.amount_netto * i.vat / 100)) - (SUM(p.amount_netto)) AS `debt`, `e`.`name` AS `user_name`, `e`.`surname` AS `user_surname`, `c`.`name` AS `contractor_name` FROM `invoices` AS `i` INNER JOIN `payments` AS `p` ON i.id = p.invoice_id INNER JOIN `employees` AS `e` ON i.employee_id = e.id INNER JOIN `contractors` AS `c` ON i.contractor_id = c.id  GROUP BY `i`.`id`) as newTable
WHERE `debt` > 1 ORDER BY `debt` ASC

Upvotes: 1

Related Questions