Exception to "SUM" expression

T

Tom

I need to come up with an exception to a SUM query. Let's say I have the
following data in a table:

===============
1.1 2
1.2 3
1.3 1
2.1 5
2.2 5

Now, based on the table information, I run a grouped query with "Sum" on the

IndexParent Value Denominator Average
=============================================
1 6 3 2
2 10 2 5

Here's how I calculated the "Average":
- IndexParent "1" has 3 children (Index 1.1, Index 1.2, Index 1.3)... this
gives me the denominator of "3"
- IndexParent "2" has 2 children (Index 2.1, Index 2.2)... this gives me the
denominator of "2"
- the value for IndexParent_1 is "6" (2 + 3 +1 = 6)... which is then divided
by its denominator... so my average = 2
- the value for IndexParent_2 is "10" (5 + 5 = 10)... which is then divided
by its denominator... so my average = 5

so far so good....

Now, I need to "tweak" my SUM query to exclude a value. Let's say I have an
answer of "9" in the table. For instance:

==============
1.1 2
1.2 9
1.3 1
2.1 5
2.2 5

As a result, here's what I get in my SUM query...

IndexParent Value Denominator Average
=============================================
1 12 3 4
2 10 2 5

Although, the query's results are mathematically correct, I need to find a
way to exclude a record when the Answer = 9.

So, my SUM query should show the following instead:

IndexParent Value Denominator Average
==============================================
1 3 2 1.5
2 10 2 5

As you can see, now I'm adding only the answers of 1.1 & 1.3 (2 + 1 =3). At
the same time, I'm subtracting "1" from the denominator field. So, instead
of dividing 3 by 3, I divide 3 by 2... .which is the correct answer.

My questions:
- if I read a certain value (9) in my answer field, how can I subtract it
from the summary total for each IndexParent?
- how can I also subtract the "number of occurrences" (where Answer = 9)
from the Denominator?
- and finally, if all answers in an IndexParent are equal to 9, I need to
show 0 (rather than divisional error that I would get when dividing 0 by 0).
How do I do that?

Tom

G

Guest

Just use your query as follow

FROM Table
GROUP BY Index

T

Tom

Ofer:

Thanks, this works great!

Ofer said:
Just use your query as follow