468,316 Members | 2,092 Online

# dividing agregate functions in a grouped query

3,405 Expert 2GB
I have a large complex query with a form something like this:
Expand|Select|Wrap|Line Numbers
1. SELECT sum(FieldA)/sum(FieldB) AS overallAverage, FieldC
2. FROM TableName
3. GROUP BY FieldC
This would give me the correct result, except that occasionally FieldB (and FieldA) is 0, so I don't get a result, I get an error. I tried to put the sum(FieldA)/sum(FieldB) in an if statement, but I couldn't get it to work. I don't know if I got the syntax wrong or what, but it said something like this
Expand|Select|Wrap|Line Numbers
1. if FieldB > 0
2. begin
3. sum(FieldA)/sum(FieldB)
4. end
5. else
6. begin
7.
8. end
9.
10. AS overallAverage
Does this make sense what I am trying to do? I need to return a result if there is no data so I can't just say "WHERE FieldB > 0", and I can't average after running the division in a subquery, because that would give an improperly weighted average. Is there some simple solution I am overlooking?

Jared
Sep 19 '08 #1
2 1120
Delerna
1,134 Expert 1GB
see if this works
Expand|Select|Wrap|Line Numbers
1. SELECT case when sum(FieldB)<>0 then sum(FieldA)/sum(FieldB) else 0 end AS overallAverage, FieldC
2. FROM TableName
3. GROUP BY FieldC
4.
Sep 19 '08 #2
jhardman
3,405 Expert 2GB
see if this works
Expand|Select|Wrap|Line Numbers
1. SELECT case when sum(FieldB)<>0 then sum(FieldA)/sum(FieldB) else 0 end AS overallAverage, FieldC
2. FROM TableName
3. GROUP BY FieldC
4.
That worked perfectly. Thank you very much!

Jared
Sep 19 '08 #3