user889349 user889349 - 5 months ago 17
SQL Question

Request from two tables with SUM

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!

Answer

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;