Howdy folks. I have a question about how to go about creating a function that will return the multiplication aggregate over a row. Essentially I need this to function in the exact same was as the sum() function, but with multiplication instead. I've been struggling trying to create a UDF to do this for me, but I'm not having much luck. I would be amazed if this is not something somebody else has already done out of necessity. Any ideas?
I've been a bit busy with the holiday so I haven't had much time to make my rounds here. So far I've learned that this essentially is not possible with anything other then ints without a udf. Currently I'm searching for a udf that will do this for me, as I am not terribly familiar with coding them myself. Perhaps it's time to up the learning curve eh?
Is that what you're trying to do? Multiply all the numbers in 1 column?
That appears to be returning null. Does the select @x = iterate through the entire recordset? I'm a bit new with variable usage in the queries themselves. Generally I would do this sort of thing at the application level with my front-end.
Originally posted by r937
hence the need for the COALESCE
and yes, you did mention this earlier, brett, but it's worth repeating -- 0 for addition, 1 for multiplication
Ok, I'm think I'm getting on the right track, but I am still not quite getting what I need. Currently I am using:
DECLARE @x FLOAT
SELECT @x = COALESCE(@x, 1) * myField
That is now returning 0.0 Oddly enough, if I use + instead of *, I return the same as SUM() + 1. So it does appear to function exactly as expected when applied to addition (the +1 is the result of using 0 as opposed to 1 for the null return), I'm still a bit stumped as to why it returns zero for multiplication. What am I overlooking?