CODEWITHSUNDEEP
sqlmysqldatabase
vize
vize

Reputation: 33

Combining IF with AVG in mySQL

I'd like to calculate the average of a column based on an if condition, but I'm getting stuck because, if a value is within a certain range, I'd like to set it to something else for the purposes of calculating the average.

Basically, I've tried variations of the following:

SELECT avg(IF(CAST(value AS SIGNED)<550, value, 100)) AS output...

In other words, I want to include only the values that are less than 550 as part of the average, otherwise return 100 for that cell as part of the average. So if I had the following values:

150, 200, 600, 650

then the query would calculate the average of these values:

100, 100, 600, 650.

Is there any way that this can be achieved? Aside from variations of the query above which don't seem to work, I've tried using an intermediate step that creates a new column of the actual values that need to be entered into the average, but this seems to be making the query much slower to execute.

Thanks!

Upvotes: 3

Views: 11848

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

select avg(case when value < 550 then 100 else value end) as output...

Upvotes: 1

The Scrum Meister
The Scrum Meister

Reputation: 30111

Just turn the IF around...,

SELECT AVG(IF(CAST(value AS SIGNED) < 550, 100, value)) AS output...

Upvotes: 4

Timothy Khouri
Timothy Khouri

Reputation: 31845

AVG(CASE WHEN value < 550 THEN 100 ELSE value END)

Upvotes: 3

Related Questions