By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

dividing agregate functions in a grouped query

jhardman
Expert 2.5K+
P: 3,405
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
Share this Question
Share on Google+
2 Replies


Delerna
Expert 100+
P: 1,134
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
Expert 2.5K+
P: 3,405
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

Post your reply

Sign in to post your reply or Sign up for a free account.