Average and Case in SQL

Question

I am trying to generate a report and the following code does not produce the desired results which gives me 2 lines rather than one.

The ScoreTypeID could have values of 22, 52, 3 or 4 . if it is 22 or 52, I need the average and if not I need to show 0. Any idea what may be the problem ? Thanks.

  CASE WHEN FAS1.ScoreTypeID = 22 THEN avg(fas1.totalscore)  
       WHEN FAS1.ScoreTypeID = 52 THEN avg(fas1.totalscore)   ELSE 0    
  END  AS   'Total Score',   

Answer 1

To keep it in the format you have chosen you could use:

CASE WHEN FAS1.ScoreTypeID IN (22,52) THEN avg(fas1.totalscore) ELSE 0
  END  AS   'Total Score', 

Answer 2

I think this is what you want:

 coalesce(avg(CASE WHEN FAS1.ScoreTypeID in (22, 52) THEN fas1.totalscore end), 0) AS   'Total Score'

This takes the average when the score is 22 or 52. If there are no values present, it returns NULL. The coalesce turns the NULL into a 0.

Answer 3

I think in your full query, you are missing the GROUP BY clause, eg

SELECT ...
FROM   ....
WHERE   ..
GROUP BY FAS1.ScoreTypeID