SELECT AVG(`col5`)
FROM `table1`
WHERE `id` NOT IN (
SELECT `id` FROM `table2`
WHERE `col4` = 5
)
group by `col2` having sum(`col3`) > 0
UNION
SELECT MAX(`col5`)
FROM `table1`
WHERE `id` NOT IN (
SELECT `id` FROM `table2`
WHERE `col4` = 5
)
group by `col2` having sum(`col3`) = 0
For readability and performance reasons, I think this code could be refactored. But how?
EDITIONS
removed the outer select
made the first select to return a sum and the second one to return another value
replaced the
SUM
byAVG