I have a MySQL request:
SELECT U.*, SUM(P.cost) AS spent FROM `#__users` AS U LEFT JOIN `#__projects` AS P ON P.client_id = U.id WHERE 1=1 AND (spent >= 1000 AND spent < 2000) GROUP BY U.id ORDER BY U.user_creation_timestamp DESC LIMIT 25 OFFSET 0;
But I have error here:
Column not found: 1054 Unknown column 'spent' in 'where clause'
How can I fix the issue? Thanks!
You can't use alias in where and you can't filter aggregation function with where for filter aggregation function you must use having
SELECT
U.*,
SUM(P.cost) AS spent
FROM `#__users` AS U
LEFT JOIN `#__projects` AS P ON P.client_id = U.id
GROUP BY U.id
HAVING (SUM(P.cost) >= 1000 AND SUM(P.cost) < 2000)
ORDER BY U.user_creation_timestamp DESC LIMIT 25 OFFSET 0;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments