is it possible to get the average value for a column, as well as the average value for the same column with a conditional? or simply to combine these two queries into one.
SELECT AVG( field ) from table
SELECT AVG ( field ) from table where col = some_val
If there isn't a simple way to combine them using native mysql functions, would a stored function be able to handle it, or a user defined function?
Answer
Taking advantage of the fact that null values are not included in aggregate functions, we can use a CASE statement to control the average, as in the following:
select avg(amt) as average,
avg(case when col=some_val then amt else null end) as conditionalAverage
from myTable;
Sample Demo: http://sqlize.com/2IXwbWD2Eb