Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    40

    Unanswered: aggregate when...

    Hi,
    I'm looking to aggregate values only under a certain condition, but do not want to influence the rest of the rows. For example, suppose I have students with grades and want to take the average however for some tests, students did not take the test. I still want to total all the tests per student regardless though.

    (students)

    student_name, test_name, test_type, grade

    select
    sum(case when test_name = 'Algebra1' then 1 else 0 end) as numalgebra,
    avg(grade) as avggrade
    from students

    Since some of the grades are null,0, it impacts the average. I'd like to see in my results

    student_name, avg grade, numalgebra
    ----------------------------------------

    but the avg grade should be for all tests not just algebra.

    thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Use NULL as the "other" value, as that won't be included by sum()
    Code:
    select sum(case when test_name = 'Algebra1' then 1 else null end) as numalgebra,
           avg(grade) as avggrade
    from students

  3. #3
    Join Date
    Feb 2010
    Posts
    40
    Thanks but the problem is that some tests have a null grade and I want to exclude those from the average calculation. Perhaps something like
    avg(case when grade != null then grade end)

    if that logic works

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    avg() will not include NULL values

  5. #5
    Join Date
    Feb 2010
    Posts
    40
    What about 0's ?
    I also have a similar problem where I am trying to average across a difference of two values and one might be 0 resulting in a negative number. In that case I want to average only when the first value (V1 - V2) is nonzero.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dtrobert View Post
    What about 0's ?
    Well 0 (zero) is not NULL, so it will be included. If you want to exclude zeros from the avg, use:
    Code:
    avg(case when grade <> 0 then grade else null end)
    Note that I'm not testing for NULL there as that will be excluded automatically.


    I also have a similar problem where I am trying to average across a difference of two values and one might be 0 resulting in a negative number. In that case I want to average only when the first value (V1 - V2) is nonzero.
    Same solution:
    Code:
    avg(case when v1 - v2 <> 0 then v1 - v2 else null end)

Posting Permissions

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