I have an sql query like this:
SELECT IF( (SELECT COUNT(*) FROM `test_table2` WHERE `id` = t.`id`) > 0, (t.`price` + (SELECT `price` FROM `attribute` WHERE `id` = t.`id` LIMIT 1)), t.`price` ) AS `full_price`, MIN(`full_price`) as `min`, MAX(`full_price`) as `max` FROM `test_table` t How can i retrieve min and max values without using duplicate code in if statement?
Thanks.
Enclose the calculation of full_price in a subquery:
SELECT MIN(full_price) AS min_full , MAX(full_price) AS max_full FROM ( SELECT IF( (SELECT COUNT(*) FROM test_table2 WHERE id = t.id) > 0 , t.price + (SELECT price FROM attribute WHERE id = t.id LIMIT 1) , t.price ) AS full_price FROM test_table AS t WHERE ... --- if you want conditions applied to `test_table` --- before calculating the `full_price` ) AS tmp ; Improvements:
CASE clause and not IF which is only for MySQL.(SELECT COUNT ...) > 0 to EXISTS (SELECT ... ). It's usually faster.Your query would become:
SELECT MIN(full_price) AS min_full , MAX(full_price) AS max_full FROM ( SELECT CASE WHEN EXISTS (SELECT * FROM test_table2 WHERE id = t.id) THEN t.price + (SELECT price FROM attribute WHERE id = t.id LIMIT 1) ELSE t.price END AS full_price FROM test_table AS t WHERE ... ) AS tmp ; 