Results 1 to 2 of 2

Thread: AVG(CASE) Statement - Help Please

  1. #1
    Join Date
    Jun 2006
    Posts
    2

    AVG(CASE) Statement - Help Please

    I am trying to get avg score by site, by call type. Columns are Site(varchar), Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1, 2, 3, 4. I can do a straight average statement and only get one calltype.
    I want to do a CASE statement to get all average scores for all calltypes.

    Select Site, avg(totalscore) as [Avg Score]
    FROM DB
    WHERE calltype = 'A'
    GROUP BY Site

    Results

    Site Avg Score (for A)
    1 85
    2 75.5
    3 85.33

    SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore
    ELSE 0 END) AS [Avg Score For A]
    FROM DB
    GROUP BY Site

    Results

    Site Avg Score For A
    1 i get 8.5
    2 i get 37.75
    3 i get 36.57
    Why am I getting a difference?
    Any help is greatly appreciated - thank you

  2. #2
    Join Date
    Aug 2007
    Posts
    3
    the problem is in the way you write your avg case.
    simply remove the else 0 and you will have the same result.

    When using the else 0 you are basically increasing the number of rows that match the casetype = 'A' because you replace a null value with 0.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •