468,316 Members | 2,092 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

dividing agregate functions in a grouped query

jhardman
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

Post your reply

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

Similar topics

reply views Thread by USI Newsgroups | last post: by
1 post views Thread by Melissa | last post: by
1 post views Thread by chip0312 | last post: by
4 posts views Thread by colin spalding | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.