MySQL Query Problem with Alias and Aggregate Functions

Question

I have a troublesome MySQL query as follows:

SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price
FROM camera_general, camera_products
WHERE camera_id = ir_camera_id
AND dp_post_dt IS NOT NULL
AND dp_post_dt NOT LIKE '0000%'
AND currently_manufactured = 'Yes'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
AND avg_price < 150 
AND camera_id != 1411
AND camera_id != 9
ORDER BY rand();

This one produced the "Unknown column 'avg_price' in 'where clause'" error. I understand this is because column aliases are not allowed in the WHERE clause. (Correct me if I'm wrong with any of this as I go, please.)

So, I tweaked the query like so:

SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price
FROM camera_general, camera_products
WHERE camera_id = ir_camera_id
AND dp_post_dt IS NOT NULL
AND dp_post_dt NOT LIKE '0000%'
AND currently_manufactured = 'Yes'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
AND ((avg(low_price) + avg(high_price)) / 2) < 150 
AND camera_id != 1411
AND camera_id != 9
ORDER BY rand();

Replacing the alias with the actual calculation and this query produced the error: "Invalid use of group function". I understand this is because the avg() can't happen until after the WHERE clause has done its processing.

So then I tried:

SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price
FROM camera_general, camera_products
ORDER BY rand();
HAVING camera_id = ir_camera_id
AND dp_post_dt IS NOT NULL
AND dp_post_dt NOT LIKE '0000%'
AND currently_manufactured = 'Yes'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
AND avg_price < 150 
AND camera_id != 1411
AND camera_id != 9;

Replacing the WHERE with the HAVING and it produced this error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING camera_id = ir_camera_id'".

And at this point, I feel like I am shooting in the dark trying to make this query work. Would someone guide me in the right direction to make this a functioning query?

Thanks!

Answer 1

ORDER clause should go after HAVING clause. (besides you put a semicolon ; after ORDER BY rand() then continued with HAVING, which is actually a start of another query, since the first got ended with the ;).

Answer 2

1. Even though you can use WHERE to specify join condition, it's better to do in LEFT[INNER] JOIN clause.
2. If you want to filter by non-aggregate field, put the filter into WHERE, if you need to filter by aggregate, move condition into HAVING

3. While using aggregate and non-aggregates in the same query, don't forget GROUP BY.

   SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price FROM camera_general
INNER JOIN camera_products ON (camera_id = ir_camera_id)
WHERE dp_post_dt IS NOT NULL
AND dp_post_dt NOT LIKE '0000%'
AND currently_manufactured = 'Yes'
AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_DAYS(dp_post_dt)) < 120))
AND camera_id != 1411 AND camera_id != 9
GROUP BY camera_id
HAVING avg_price < 150
ORDER BY rand();