What Was I Thinking?

Follies & Foils of .NET Development
posts - 95 , comments - 352 , trackbacks - 0

How to get a Product() (multiplication result) rather than a SUM() (addition result) using T-Sql

Get a summary aggregation of rows in T-Sql is easy thanks to the Sum operator:

Select Sum(Qty) From Table

Why is there no Product() aggregation operation for T-Sql?  Sometimes I want the values multiplied, not added.

 

Luckily, some one who is much smarter in math than I, observed:

log(A * B) = log(A) + log(B)

So, summing the log, and converting back to its exponential value will yield its product.

Select CAST(EXP(SUM(LOG(Qty))) as int) as ExtendedQTY

Happy Calculating!

 

UPDATE: The above expression seems to calculate the wrong value when the value being multiplied is a large number.  For example:

Select CAST(EXP(LOG(11111)) as int)  yields 11110, not 11111.

 

Try this instead:

  DECLARE @ExtendedQty FLOAT

Select  @ExtendedQTY = COALESCE(@ExtendedQTY, 1) * Table.Qty From Table

Select @ExtendedQty

As always, your feedback is welcome.

Print | posted on Wednesday, February 29, 2012 2:32 PM | Filed Under [ SQL ]

Feedback

Gravatar

# re: How to get a Product() (multiplication result) rather than a SUM() (addition result) using T-Sql

Casting to MONET for large numbers yields the correct result:

Select CAST(EXP(LOG(11111)) as MONEY) = 11111.00

You can cast back to int if desired.
2/4/2013 2:28 AM | Joe
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

Powered by: