doing an aggregat inside another IFed aggregate...

[FederalProgrammer]

All right, let's state what I need first:
Consider a number X; here's how we get it:
Select Sum(X)
From tblBlah inner join millionOtherTables
Group BY tblBlah.someField

The Query goes through the following logic:
If contion1 is true do the following:
X/aTable.Field if contion2 is true;
X/anotherTable.Field if condition2 is false and anotherTable.Field <> 0;
If condition1 is false, simply return X.

I've got all the (IIFs) figured out, and this is not the problem: here's what i do:

SUM(iif(Cond1,
iif(cond2, x/aTable.Field, iif(anotherTable.field = 0, 0, x/anotherTable.field)), X)).

Easy enought.. right? WRONG!!
You see the divisor, aTable.Field; Well, I don't need its value, as is; I need to divide X by the total of aTable.Field for that particular group.
I've tried the following:
x/Sum(aTable.Field). However access states that I cannot have aggregate functions inside iif! I have tried moving IIFs outside of SUM; The first IIF goes through; However the 2nd one complains about the fact that "iif(Cond2" is not part of the "Group By" or whatever;
Anyways, if you've understood the problem, please drop me a line. it means a lot to have someone on the same page as me!

cheers!

 

[rac2]

Here are some thoughts that might help you sort this out.

IIf() is like a re-code. It looks at a value and returns one of two possible other values. It works on a value from a single row.

Select Sum(X)
From tblBlah inner join millionOtherTables
Group BY tblBlah.someField

This yields many numbers, one for each value of someField.
It could not be the definition of X because it operates on X.


Complex problems in SQL can be broken into pieces by defining queries that capture the sense of parts of the final result; and these queries can be saved by name and used as if they were tables.

If contion1 is true do the following:
X/aTable.Field if contion2 is true;
X/anotherTable.Field if condition2 is false and anotherTable.Field <> 0;
If condition1 is false, simply return X.

If X were the name of a column and it were in a table named mySavedQuery which could be JOINed to aTable or anotherTable you could write

SELECT IIf( a.contion1, 
            IIf( AND(a.contion2, b.Field = 0)
                 sq.X/a.Field,
                 sq.X/b.Field ),
            
            sq.X )
FROM mySavedQuery sq
JOIN aTable a ON a.id = sq.id
JOIN anotherTable b ON b.id = sq.id

 

[FederalProgrammer]

Thanx for the tip...
But I must insist on using one single query because of the nature of my tool!
thanx for explaining the iif() function. I do have the same understanding of it as you explained.
The important thing for me is to be able to write this query on a single query... It is due to what my tool is using this query....

cheers!

 

[rac2]

What database and what tool?

IIf not Access, what?

A saved query is a VIEW by another name.

 

[FederalProgrammer]

MS Access