| Author |
Topic |
|
KidSQL
Yak Posting Veteran
88 Posts |
 Posted - 2008-05-22 : 14:39:35
|
| Hi guys,Does anybody know a convenient way to return the product of a column, similar to something like the 'sum' function?If I have a table of form id-date-value, I'd like to do something like:select id, date, product(val)If not, does anyone know of a way one could structure this as a subquery?If all else fails, I suppose I'll have to resort to a loop or cursor, but I'd rather not.Any help would be much appreciated. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-22 : 20:32:56
|
| the cleanest way to do that would probably be using functionality new to 2005 by creating your own Aggregate function (CLR) So the code would actually be written in C# (or whatever .net code you want). I haven't actually done this yet but there are a lot of articles out there and it looks pretty simple (and efficient).Be One with the OptimizerTG |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-23 : 08:10:22
|
You have to beware that this only works for positive non-zero numbers!The math behind is simple.Any positive non-zero number x can be written as e^(log x)Thus 12 * 31 can be written as e^(log 12) * e^(log 31) which can be simplified with e^(log(12) + log(31))which in SQL Server is written EXP(SUM(LOG(x)))
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-23 : 08:17:27
|
To get around zero values and negative values, try thisIF EXISTS (SELECT * FROM Table1 WHERE Col1 = 0) SELECT 0.0ELSE SELECT CASE IsNegativeProduct WHEN 1 THEN -EXP(theSum) ELSE EXP(theSum) END FROM ( SELECT SUM(LOG(ABS(Col1))) AS theSum, SUM(CASE WHEN Col1 < 0 THEN 1 ELSE 0 END) % 2 AS IsNegativeProduct FROM Table1 ) AS d
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-26 : 09:34:47
|
Hm, I was always using numbers > 1 so this was never a problem for me. Would have made things way more complcated if I'd have to take negative numbers in account! But Pesos solution seems to do the trick (as always). And that modulus-operation for finding out if the final result was positive or negative was quite nice...simple but does the trick  --Lumbago |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-26 : 10:17:54
|
Thank you.BTW, congrats to the 5th place in ESC.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-26 : 10:24:28
|
| Who got 5th place in what?--Lumbago |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-05-26 : 10:25:01
|
| http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/13/simple-multiply-function.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-26 : 10:27:45
|
quote:
Originally posted by Lumbago
Who got 5th place in what?
You ended up in fifth place in the Eurovision Song Contest this weekeend.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-26 : 10:29:07
|
Ah, had to google "ESC" and then I got it hehe...thanx. I actually watched parts of it and I must say that I've never seen so many short skirts in one place. The music was not exactly my taste but I sure enjoy a good freakshow and miniskirt/cleavage  --Lumbago |
|
|
|
|
Product as Aggregate Function?