CODEWITHSUNDEEP
mysqlsqlaverage
Jenny
Jenny

Reputation: 1727

How to do conditional average?

I want to calculate average result by post_author. Here's my code: 

AVG(CASE WHEN post_author = 1 THEN post_content ELSE 0 END )as avg

It got a result of 100 on 2 records (100, 66.7) which should be 83.35 Where things went wrong?

Upvotes: 3

Views: 8939

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can just drop the else clause, so NULL is passed in:

AVG(CASE WHEN post_author = 1 THEN post_content END) as "avg"

Your query is setting unmatching values to 0 for the purposes of the average. You want them ignored, so use NULL.

Upvotes: 7

Related Questions