CODEWITHSUNDEEP
mysqlaverage
Rob van den Eijnden
Rob van den Eijnden

Reputation: 23

How can i calculate the average of fields and exclude the 0 values

I have this:

SELECT
    ROUND(AVG(coffee)) 'coffee',
    ROUND(AVG(cappucino)) 'cappucino',
    ROUND(AVG(espresso)) 'espresso',
    ROUND(AVG(machine)) 'machine'
    FROM `reviews` ORDER BY `username`

My Table looks like:

+-----+-----------+---------------+---------------+---------------+---------------+
| id  | name      | coffee        | cappucino     | espresso      | machine       |
+-----+-----------+---------------+---------------+---------------+---------------+
|  1  | Joe       | 5             | 4             | 5             | 4             |
|  2  | Jane      | 3             | 5             | 2             | 5             |
|  3  | Mike      | 0             | 0             | 0             | 5             |
+-----+-----------+---------------+---------------+---------------+---------------+

I need the average of the reviews but Mike doesn't drink any coffee and he clicked the "Does not apply" button. So the current average is:

coffee 2.66666667
cappucino 3
espresso 2.33333333
machine 4.66666667

But it should be:

coffee 4
cappucino 4.5
espresso 3.5
machine 4.66666667

How can I figure this out?

Upvotes: 1

Views: 134

Answers (3)

ysth
ysth

Reputation: 98388

NULL values are ignored by AVG, so do AVG(NULLIF(coffee,0)). Or store NULL instead of 0 in the first place.

Upvotes: 1

Rob van den Eijnden
Rob van den Eijnden

Reputation: 23

This was the fix:

SELECT
    ROUND(AVG(NULLIF(coffee,0))) 'coffee',
    ROUND(AVG(NULLIF(cappucino,0))) 'cappucino',
    ROUND(AVG(NULLIF(espresso,0))) 'espresso',
    ROUND(AVG(NULLIF(machine,0))) 'machine'
    FROM `reviews` ORDER BY `username`

Upvotes: 0

Ayush Jain
Ayush Jain

Reputation: 376

This is definitely a great question. I have come up with a solution.

 select avg(coffee) from data where coffee>0 union select avg(cappucino) from data where cappucino >0 union select avg(expresso) from data where expresso>0 union select avg(machine) from data where machine>0;

Try this. I'm calculating averages of all fields separately using the where condition that the value in the field should be greater than 0 and then I am displaying the UNION of all queries.

This will be the output... To avoid getting avg(coffee) as the column header, you may use ALIAS.

+-------------+
| avg(coffee) |
+-------------+
|      4.0000 |
|      4.5000 |
|      3.5000 |
|      4.6667 |
+-------------+

I hope this was helpful.

Upvotes: 0

Related Questions