Jul 4

Written by: Bernhard Zehetgruber
7/4/2008 12:38 AM

Building the SUM, MIN, or MAX .. of over a grouped set of Records this is very easy.

Invenstment | Year  | InterestPrevYear
--------------------------------------
Warehouse_1 | 2004  | 1,05
Warehouse_1 | 2005  | 1,2
Warehouse_1 | 2006  | 1,3

SELECT investment, SUM(InterestPrevYear)
FROM sales.revenue_year
GROUP BY investment

--> Warehouse_1 | 3,55

We want to build the product to charge the interest over the last 3 years, the SUM or any other kown aggregte function is not correct in this case.
We must build the product to charge the interest!

Remember: a*b*c = exp(Log(a*b*c )) = exp(log(a) + log(b) + log(c))

SELECT investment, EXP(SUM(LOG(PercRevenueYear)))
FROM sales.revenue_year
GROUP BY investment

--> Warehouse_1 | 1,638

THIS IS IT!!

Tags: