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?

score 36 · Accepted Answer · answered Nov 15 '11 at 01:14

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

score 13 · Answer 2 · answered Nov 25 '14 at 17:30

13

There is another way, not using case when

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

answered Nov 25 '14 at 17:30

kapiva

157
1
4

mysql avg on conditional

2 Answers2