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:
