ms access - Aggregate multiplicate function

Question

I have a table of the following:

1    X    10
2    X    30
3    Y    5
4    Y    2
...etc

I need to turn it into:

X    300   //(10 * 30)
Y    10    //(5 * 2)

so I'm in fact looking for a kind of a multiplication function that I could use like sum, avg and stuff... does this exist guys?

for instance:

select field2, **multiply**(field3)
from t
group by field2

Thanks

Answer 1

Ideally, Access SQL would have a PRODUCT aggregate function available, but it doesn't. We can however simulate it by remembering what we learned about logarithms at school (or not...), and remembering that the anti-log of the sum of logs is equal to the product:

SELECT field2, EXP(Sum(LOG(Field3))) AS ProductOfField3
FROM t
GROUP BY Field2

Note that whereas a true PRODUCT function would simply return 0 for a group if there are any zero values, this solution will fail if there are any zero values, so watch out for that. Also, this approach won't work if there are any negative values.

To deal with zeroes we could do this:

SELECT
    field2, 
    EXP(Sum(LOG(IIf(Field3 = 0, 1, Field3)))) AS ProductOfField3,
    MIN(ABS(Field3)) AS MinOfAbsField3
FROM t
GROUP BY Field2

and then disregard the ProductOfField3 value for any row where MinOfAbsField3 is zero (as this indicates a group containing a zero, thus the 'true' product should be 0)

To deal with negative values we could further do this:

SELECT
    field2, 
    EXP(Sum(LOG(IIf(Field3 = 0, 1, ABS(Field3))))) AS ProductOfField3,
    MIN(ABS(Field3)) AS MinOfAbsField3,
    SUM(IIf(Field3 < 0, 1, 0)) AS SumOfNegativeIndicator
FROM t
GROUP BY Field2

and interpret the results with these rules:

• If MinOfAbsField3 is zero, disregard ProductOfField3 for that row - the product is zero
• Otherwise, the required answer for a given row is ProductOfField3, negated if SumOfNegativeIndicator is odd in that row