﻿ SQL statement to aggregate multiplied rows

# SQL statement to aggregate multiplied rows

Posted by Job-Interview

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: