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

Swinge

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

Rivi

Thanks AakashM. I tried it this way but it didn't work, it keeps saying "Invalid procedure call" and I couldn't spot where the error is coming from exactly. I'm though sure it's not coming from the rest of my query, seems access doesn't like EXP(Sum(LOG([X]))) or whatever. I ended up using a crosstable since I do only have at most 3 columns to multiplicate, but i'll keep this noted for future needs. – Swinge

@Swinge that is the error you will get if any of the values are zero (or negative). I will update to indicate one way to deal with this situation – Rivi

+1. Another awkward case is if you are not grouping but computing the product of a whole table or result set that is empty. The product should be 1. – Unstick

I'm wondering why EXP(Sum(LOG(Nz(Field3,0)))) wouldn't take care of the Null problem? – Duello

Nz(Field3, 1) would solve the problem. the natural logarithm of zero doesn't exist. – Swinge

Answer 2

You can define following two functions:

Public Function ResetProd() As Boolean
    Call Prod(Null)
    ResetProd = True
End Function

Public Function Prod(nNumber As Variant) As Double
    Static nPrevNumber As Double
    If IsNull(nNumber) Then
        nPrevNumber = 1
    Else
        nPrevNumber = nPrevNumber * nNumber
    End If
    Prod = nPrevNumber
End Function

...and use them as follows:

SELECT PROD(_column_for_wich_you_want_product_) FROM _your_table_
WHERE RESETPROD()

Burkes

This looks nice in theory, but have you actually tried it? It doesn't really work for me (some rows are multiplied more than once), and mainly: it doesn't aggregate. And you can't use GROUP BY. – Rebuke