Full question:

Given a SQL table which has three columns: Name, Amount and Date the task is to write a SQL statement to aggregate rows in a such way that each row has unique Name along with multiplied values of Amount cell for that name (see picture below for details).

Solution:

SQL aggregate functions return a single value, calculated from values in a column. At the first glance the question seems to be very trivial usage of SQL aggregates. However, along with all available aggregate functions there is no multiplication.

Transact-SQL provides the following aggregate functions:

AVG

MIN

CHECKSUM_AGG

COUNT

ROWCOUNT_BIG

COUNT_BIG

STDEV

GROUPING

STDEVP

GROUPING_ID

SUM

MAX

VAR

VARP

Again, no multiplication.

Aggregate Functions (Transact-SQL)

So, the solution would be to utilize summation to achieve multiplication. From math 101 you might remember that the logarithm of a number is the exponent by which a fixed number, the base, has to be raised to produce that number.

__From that definition:__

X = EXP(LOG(X)), i.e if we take exponent from logarithm of x we will get same x.

The rest is simple, by using properties of logarithms that the logarithm of a product is the sum of the logarithms of the factors:

LOG(X * B) = LOG(X)+LOG(Y)

Hooray ! We got to the sum ! Finally combining those two formulas together, we have

X*Y = EXP(LOG(X)+LOG(Y))

Thus, the solution would be surprisingly simple SQL statement:

###

Answers and Comments