Exception to "SUM" expression

[Tom]

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

Index Answer
===============
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
"Answer" field.

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:

Index Answer
==============
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?


Thanks in advance,
Tom

 

[Guest]

Just use your query as follow

SELECT Index , Sum(Answer) AS SumOfAnswer
FROM Table
WHERE Answer<>9
GROUP BY Index

 

[Tom]

Ofer:

Thanks, this works great!

Just use your query as follow

SELECT Index , Sum(Answer) AS SumOfAnswer
FROM Table
WHERE Answer<>9
GROUP BY Index