I have a large complex query with a form something like this:
- SELECT sum(FieldA)/sum(FieldB) AS overallAverage, FieldC
-
FROM TableName
-
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
- if FieldB > 0
-
begin
-
sum(FieldA)/sum(FieldB)
-
end
-
else
-
begin
-
-
end
-
-
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