mysql avg on conditional

Question

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 1

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

Answer 2

There is another way, not using case when

select 
   avg(amt) as average, 
   avg(if(col=some_val,amt,null)) as conditionalAverage
 from myTable