CODEWITHSUNDEEP
mysqlaverage
Eric Pigeon
Eric Pigeon

Reputation: 1131

mysql avg on conditional

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?

Upvotes: 17

Views: 18583

Answers (2)

kapiva
kapiva

Reputation: 197

There is another way, not using case when

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

Upvotes: 17

mellamokb
mellamokb

Reputation: 56769

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

Upvotes: 38

Related Questions