# Thread: aggregate when...

1. Registered User
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. Registered User
Join Date
Nov 2003
Posts
2,993
Provided Answers: 23
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. Registered User
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. Registered User
Join Date
Nov 2003
Posts
2,993
Provided Answers: 23
avg() will not include NULL values

5. Registered User
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. Registered User
Join Date
Nov 2003
Posts
2,993
Provided Answers: 23
Originally Posted by dtrobert
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
•