There are two SQL queries. The first is performed without problems:

SELECT DISTINCT self.*, (SELECT COUNT(*) FROM `ps_orders` WHERE self.`id_customer` != 'NULL' AND `id_customer` = self.`id_customer`) AS orders_total FROM `ps_module_actionssubscription` AS self LIMIT 0,20;

And on the second MySQL query, I swear at Error Code: 1054. Unknown column 'orders_total' in 'where clause' :

 SELECT DISTINCT self.*, (SELECT COUNT(*) FROM `ps_orders` WHERE self.`id_customer` != 'NULL' AND `id_customer` = self.`id_customer`) AS orders_total FROM `ps_module_actionssubscription` AS self WHERE orders_total > 5 LIMIT 0,20;

Where is the error in the second request ?? How to write it down correctly so that it returns only results on the WHERE condition ??

  • 3
    Try to replace WHERE with HAVING - Alexey Shimansky
  • Essna, as he makes a selection of what is not yet. if you just want a similar query structure, wrap it in SELECT * FROM (SELECT DISTINCT self. *, .... then the order_total will already be known. and something I have doubts about the correctness! = NULL. But in general there is little basic information, hence ridiculous sentences. - ar3409
  • Alexey Shimansky, thank you, replacing WHERE with HAVING helped. Issue as an answer, then put a tick)) - GHosT

1 answer 1

The documentation en / ru says:

It is not possible to determine if it is not allowed. See Section B.1.5.4, “Problems with Column Aliases” .

I mean

Column aliases cannot be used in the WHERE clause, since the values ​​in the columns at the time WHERE is executed may not yet be defined. See section A.5.4 Problems with alias.

Together with that

The HAVING expression can refer to any column or alias mentioned in the select_expression expression. HAVING processed last, immediately before sending data to the client, and without any optimization.

and

Aliases can be used to refer to a column in GROUP BY, ORDER BY or in the HAVING part, as well as for better naming of columns.

It turns out for aliases in this case should be used HAVING instead of WHERE .

PS For a brief introduction I suggest to get acquainted with the question on enSO WHERE vs HAVING