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 conditional column? or just combine these two queries into one.

SELECT AVG( field ) from table SELECT AVG ( field ) from table where col = some_val 

If there is no easy way to combine them using mysql native functions, can a stored function 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 the CASE operator to control the average value, as shown below:

 select avg(amt) as average, avg(case when col=some_val then amt else null end) as conditionalAverage from myTable; 

Demo example: http://sqlize.com/2IXwbWD2Eb

Answer 2

There is another way, not using the case where

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