473,320 Members | 1,746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

dividing agregate functions in a grouped query

jhardman
3,406 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 1280
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,406 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

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

Similar topics

0
by: USI Newsgroups | last post by:
Struggling to write this script: DESCR TYPE SELL StartDate EndDate Number 65048 04 Price A 4/21/2004 4/26/2004 35456 65048 06 Price...
1
by: Melissa | last post by:
Can anyone help me with this --- I have a grouped report with a subreport in the group footer and the report starts a new page for each group. The subreport is based on a query which has...
1
by: chip0312 | last post by:
I am trying to group my avergaes by month. However, when I run the query, I received the averages but by day. I neeed to group these averages by month. Thanks in advance for your help. The table...
16
by: Martin Jørgensen | last post by:
Hi, Problem: ======== Some of my output functions are beginning to take pretty many arguments... I mean.... We're talking about 10-15 arguments :-) So I thought to myself, perhaps this is...
3
by: yoyo35 | last post by:
I am new to SQL. I am trying to write a query that will join two tables. The join works fine, however I am getting incorrect results. I would like the joined tables to be grouped by ProposalId for...
8
by: nico3334 | last post by:
I have a database that has a Date column (1/1/2007) and a Data column that has numerical data. I am currently running a query to sum the Data column for a certain month and grouped by day (There is...
1
MMcCarthy
by: MMcCarthy | last post by:
Access has a number of built-in functions which can be generally used in queries or VBA code. Some of the more common ones are: Note: anything in square brackets is optional Date Functions ...
4
by: colin spalding | last post by:
Access 2003 I posted this last week; unfortunately, none of the suggested solutions cured the problem I have a subform which lists financial transactions for a client in the main form, which...
1
by: jeannin | last post by:
I have a table with the following text fields Status group_type query_hits PLATFORM Group_No_7 Group_No_3 I need to be able have a form which uses combo boxes as required and optional...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.