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

Use NULL as the "other" value, as that won't be included by sum()

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

avg() will not include NULL values

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.

Well 0 (zero) is not NULL, so it will be included. If you want to exclude zeros from the avg, use:Note that I'm not testing for NULL there as that will be excluded automatically.


Same solution: