multiplication by row without a cursor? Myth or Madness?

Hi all...I have a friend who has a problem I'm trying to help with (no...REALLY...it's a FRIEND, not ME!!! *LOL*)

I haven't run across the need...but in a nutshell...we have a table with many rows of data, one column in each row needs to be multiplied by all other same-columns in the table's other rows.

For example...
MyKey int, MyFloat float(53)

I want to multiply Myfloat by all other Myfloat columns in the table.

Similar to SUM(MyFloat) but something like PRODUCT(MyFloat).

Is there a aggregate kept in a basement closet somewhere, or a way to perform this operation rather than using a cursor to do it:

An example of my table:
1 3.2
2 4.1
3 7.1

if I could do a PRODUCT(MyFloat) I would want the result to be (3.2 X 4.1 X 7.1) or 93.152

Do I have to do this with a cursor?

Thanks!
PaulGot it...
DECLARE @.MyInt int
SET @.MyInt = 1
SELECT @.MyInt = @.MyInt * MyFloat from MyTable where MyKey = <whatever>
print @.Myint

Yeah, I know...it's an overflow waiting to happen...but still...

Thanks for reading...and sorry to talk to myself yet again...I guess my Mom was right when she told me that I'm just a waste of bandwidth... :(|||What, did nobody pay attention during High School math classes?

select POWER(10, sum(log10(MyFloat))) from MyTable|||I have a harder time believing he has a freind...

:D

Just joking of course...|||Whee!!! The forum is working again!!! (I HEARD that, Brett...and a DOG and 3 tortoises still count as friends!)

Thanks for the suggestion, Blindman...I guess all that time and money was wasted in my math classes! :) Ya know, somehow, I just KNEW I shoulda paid more attention in my LOG class :D

However...(hey, you knew this was coming) that doesn't work, of course, for the negative numbers I've got in my "product" table...

I know a select can be written to check the sign of each number included in the calculation, but before I go and burn an excessive number of brain cells (or search keystrokes) anyone have a handy-dandy code block avialable for that part of the calculation?

It will, I am reasonably certain, require merely counting the number of negative numbers in the calculation and an ABS, like:
select POWER(10, sum(log10(ABS(MyFloat)))) from MyTable
Then somehow applying the sign ( ans * -1 for odd number of negative MyFloat entries in the calculation) to the result, but I am presently at a loss for how to apply the sign aspect of the problem.

Off to the races...

Thanks again!
Paul|||I imagine it would be as simple as:

select POWER(10, sum(log10(ABS(MyFloat))))*sign(MyFloat) from MyTable

My only problem with it is that if you have a 0 anywhere in the column, you could end up with problems, too. I think for simplicity sake, you should go with your first query. Of course, SQL 2005 is bound to disable Blindman's query that it was based on, since it has proven itself to be way too useful.|||OK, from what I have been able to ascertain (yep, just bought a new dictionary, thankyouverymuch! ;) ) the proposed addition of SIGN(MyFloat) won't work because that isn't (at least as written) an aggregate, so the compiler complains about it being invalid in the select list 'cause there is no GROUP BY nor is it in an aggregate.

Neither can it be added into the POWER(xxx) expression - because, again, POWER() apparently maintains a strong and lasting hatred of negative numbers - preferring to relegate them to nothingness rather than deal with them in a play-nice-in-the-sandbox way. But I digress...

What I needed was a way to apply the sign AFTER the PRODUCT-like operation.

What I found was several web hits that had logic for PRODUCT-type implementations in SQL that were NEAR what I wanted...and so...without further delay, here is my final product:

SELECT (EXP (SUM (LOG (CASE WHEN MyFloat = 0.00
THEN NULL
ELSE ABS(MyFloat) END))))
* (CASE WHEN (SUM (CASE WHEN SIGN(MyFloat) = -1
THEN 1
ELSE 0 END)) % 2 = 1
THEN -1.00
ELSE 1.00 END) AS Prod
FROM MyTable

The first CASE handles the situation of having NULL or Zero values in the table (which I simply want to ignore).

The second CASE applies the sign to the result, by keeping track of the NUMBER of negative values, and then multiplying the EXP(SUM(LOG())) overall result by -1 if the COUNT of negative values is odd, or by 1 if the COUNT is even.

I wish I could take credit for this, but alas, just for a workable b@.st@.rdization of the work of other folks ;)

I still am bothered by the complexity...I mean, if the calculation changes a year from now, I KNOW they are gonna send that Jr. Programmer to ask me about what the HEY I was thinking, or else just have him/her make changes without knowing what it does to start with (though I'm SURE that never happens ;) ).

The whole thing is further complicated because I (err...my "friend", I mean) have to perform some other calculations on the value returned from the above select...and if I was to use my original method, I'd have to make two passes through the table. That may be preferable to the relative complexity of this select...but I'll try it and see if my peers shoot me down in the code review ;)

Thanks guys!!! Great soundboarding!

Oh, and it REALLY started out to be the question of a co-worker working on the same project as I...it became mine when it took me too long to get back to her with a "I would do this..." email... :(|||Accounts for negative values and nulls:

------------------
set nocount on
declare @.PRODUCT table (Product varchar(30), Value float)

insert into @.Product (Product, Value) Values ('Coffee', -4.4)
insert into @.Product (Product, Value) Values ('Coffee', -5.5)
insert into @.Product (Product, Value) Values ('Coffee', 6.6)
insert into @.Product (Product, Value) Values ('Coffee', 7.7)
insert into @.Product (Product, Value) Values ('Tea', -44)
insert into @.Product (Product, Value) Values ('Tea', 55)
insert into @.Product (Product, Value) Values ('Tea', 66)
insert into @.Product (Product, Value) Values ('Tea', 77)
insert into @.Product (Product, Value) Values ('Water', -44)
insert into @.Product (Product, Value) Values ('Water', 55)
insert into @.Product (Product, Value) Values ('Water', 66)
insert into @.Product (Product, Value) Values ('Water', 77)
insert into @.Product (Product, Value) Values ('Water', 0)

select Product, EXP(sum(log(abs(nullif(Value, 0))))) *
(1+2*(cast(sum(sign(Value)-1)/2 as int) % 2)) * min(abs(sign(value)))
from @.PRODUCT
group by Product
------------------
This is truly mental mssql-bation...|||Hi, this is Paul's boss...thanks for exploding his head...

Now..WHO is going to cleanup this mess? ;)|||Well, if you'll check out my title I AM the Electronic Janitor.

Now where did I put my Electro-mop...?

Multiplication Aggregate

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?Maybe you can show what formula you want to apply?|||Ok, how about:

USE Northwind
GO

CREATE FUNCTION udf_MULT (@.x float)
Returns float
AS
BEGIN
DECLARE @.y float
SELECT @.y = @.x * 2
RETURN @.y
END

GO

SELECT Freight, dbo.udf_MULT(Freight) FROM Orders
GO|||Brett, i don't think your udf_MULT is what Teddy wants

sample rows:

name value
foo 2
bar 4
qux 6
fap 8

select sum(value) from ... gives 20

select mult(value) from ..., assuming there were such an aggregate function as "mult()", would return 384

maybe a variation of that routine written up in that sqlteam article using coalesce to produce a comma-delimited list?

rudy|||You mean like:

USE Northwind

DECLARE @.x money
SELECT @.x = ISNULL(@.x,1) + Freight FROM Orders
SELECT @.x

I added them because of overflow...but you could use * instead...|||mmm, i love overflow

perhaps that's why "mult()" was never invented -- too easy to blow up the query real good

:)|||Add for the +, the ISNULL value should be 0, not 1...1 For Multiplication

hmmmmmmmmm...my cup runneth over with a flaming homer...or was that a flaming moe...

Hey...seeing it in type...

Noooow I get it...

Great episode...

Got to pick me up some cough medice on the way home...|||Thanks for the reply guys!!

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?|||a MULT function for varchar?

Hmmmm, now you've got me TOTALLY lost...

What exactly are you trying to do?|||Originally posted by Brett Kaiser
a MULT function for varchar?

Hmmmm, now you've got me TOTALLY lost...

What exactly are you trying to do?

Not for varchar, float. The closest I've found so far is sort of a bizarre workaround that doesn't seem to work properly over large recordsets:

EXP(SUM(LOG(value)))|||Originally posted by Brett Kaiser
What exactly are you trying to do?

Do you mean like what r937 said?

You can't (to my knowledge) create a user defined scalar function...

But you can employ the other method I posted

DECLARE @.x bigint
SELECT @.x = @.x + floatColumn FROM Table
SELECT @.x

Is that what you're trying to do? Multiply all the numbers in 1 column?|||Originally posted by Brett Kaiser
Do you mean like what r937 said?

You can't (to my knowledge) create a user defined scalar function...

But you can employ the other method I posted

DECLARE @.x bigint
SELECT @.x = @.x + floatColumn FROM Table
SELECT @.x

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.|||Give this a read...

http://sqlteam.com/item.asp?ItemID=2368|||That appears to be returning nullhence the need for the COALESCE

and yes, you did mention this earlier, brett, but it's worth repeating -- 0 for addition, 1 for multiplication

:D|||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

:D

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
FROM myTable

SELECT @.x

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?|||overlooking? probably precision and/or scale (i can never remember which is which)

try

SELECT @.x = COALESCE(@.x, 1.000000000) * myField|||Originally posted by r937
overlooking? probably precision and/or scale (i can never remember which is which)

try

SELECT @.x = COALESCE(@.x, 1.000000000) * myField

The issue was an overflow error. I ran the same syntax over a much smaller dataset and produced the desired result.

Thanks a bunch guys!!

This is at least enough to get me over the "how the hell..." hump that everyone hits once in a while.

Much obliged.|||And just for another twist...what if 1 of your rows has a 0

Come on...

puuuuleeeeeeeeeeeeze

Tell us this is more than an academic exercise...because I can't think of a practical application at all...|||Originally posted by Brett Kaiser
And just for another twist...what if 1 of your rows has a 0

Come on...

puuuuleeeeeeeeeeeeze

Tell us this is more than an academic exercise...because I can't think of a practical application at all...

The practical application is to a series of modifiers to an insurance quote. If one of them is zero, then the quote is free. I doubt that will happen.

:)|||So you have a very specific predicate...the quote...how many modifiers per quote on average?

...and if it can happen it will happen...just a matter of time...

CREATE TABLE myTable99 (Col1 int NOT NULL CONSTRAINT myTable99_chk1 CHECK (Col1 <> 0))|||Originally posted by Brett Kaiser
So you have a very specific predicate...the quote...how many modifiers per quote on average?

...and if it can happen it will happen...just a matter of time...

CREATE TABLE myTable99 (Col1 int NOT NULL CONSTRAINT myTable99_chk1 CHECK (Col1 <> 0))


approx 7-8 modifiers per quote. The mods tend to be in the .75 - 1.5 range. And it will never be 0. Not unless the chairman of the board owes someone a serious favor. In which case, that's really not my problem to begin with.

The issue is the varying number of modfiers for each quote. Some will only have one, some will have 8. That's why this mult function comes in handy. This is fairly easy to do client-side, but for some reason is murderous to do in sql until this morning.|||So you would check to see if the result of this multiplier function is zero, and then act accordingly?

There are other methods that might work. For instance:

select min(Abs(YourColumn))

...would return zero if any of the values is zero.

blindman|||...and you can include this directly in a calculation:

Cost * cast(cast(min(Abs(YourColumn)) as bit) as int)

...would result in a cost of $0 if any of the multipliers is zero.

blindman|||Originally posted by blindman
...and you can include this directly in a calculation:

Cost * cast(cast(min(Abs(YourColumn)) as bit) as int)

...would result in a cost of $0 if any of the multipliers is zero.

blindman

That's really not an issue. This is well and solved.

Anywho, the current method would return 0 as-is.|||Originally posted by Teddy
Not for varchar, float. The closest I've found so far is sort of a bizarre workaround that doesn't seem to work properly over large recordsets:

EXP(SUM(LOG(value)))

I do like the workaround solution. Could you tell me what do you mean by "doesn't seem to work properly over large recordsets"? Is it the overflow problem? I tested it on a table of 4000 records, the results is as expected.|||Originally posted by shianmiin
I do like the workaround solution. Could you tell me what do you mean by "doesn't seem to work properly over large recordsets"? Is it the overflow problem?

edit: read that wrong.

I'm not sure what the issue is. It returns nothing at all when I use a large set. Very odd.|||Originally posted by Teddy
edit: read that wrong.

I'm not sure what the issue is. It returns nothing at all when I use a large set. Very odd.

for actual cases, i.e.

approx 7-8 modifiers per quote. The mods tend to be in the .75 - 1.5 range. And it will never be 0. Not unless the chairman of the board owes someone a serious favor.

I think this would be a best solution to me. :)

select
case
when min(isnull(f1, 0))=0 then 0
else exp(sum(log(isnull(case when f1=0 then 1 else f1 end,1))))
end
from t1

The query posted handles zero and null value correctly.
And I don't think overflow would be a problem.
(There shouldn't be negative values in the table, if it is, you need to add some code to handle it properly)|||The issue is:

USE Northwind
GO

DECLARE @.x BIGINT
SELECT @.x = 1
--Wont Blow Up
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 1
SELECT @.@.ERROR AS ErrorCode
--Will Blow Up
SELECT @.x
SELECT @.x = 1
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 2
SELECT @.@.ERROR AS ErrorCode
SELECT @.x

--The error should read:
-- Server: Msg 8115, Level 16, State 2, Line 11
-- Arithmetic overflow error converting expression to data type bigint.

In any event, error checking is a must...for all code...|||Originally posted by Brett Kaiser
The issue is:

USE Northwind
GO

DECLARE @.x BIGINT
SELECT @.x = 1
--Wont Blow Up
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 1
SELECT @.@.ERROR AS ErrorCode
--Will Blow Up
SELECT @.x
SELECT @.x = 1
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 2
SELECT @.@.ERROR AS ErrorCode
SELECT @.x

--The error should read:
-- Server: Msg 8115, Level 16, State 2, Line 11
-- Arithmetic overflow error converting expression to data type bigint.

In any event, error checking is a must...for all code...

Since the query returns float, the range would be - 1.79E + 308 through 1.79E + 308, that's what I meant that overflow shouldn't be a problem for actual situration. To prevent errors of overflow, the query may check the value before applying EXP(), that would be something like

case
when sum(log(...)) > 307 then ... -- overflow
when sum(log(...)) < -307 then ... -- underflow
else exp(sum(log(...)))
end|||Ahhh proactive error handling...very nice...

just watch...the [banging head]overhead[/banging head] for some statements...|||Originally posted by Brett Kaiser
Ahhh proactive error handling...very nice...

just watch...the [banging head]overhead[/banging head] for some statements...

I agree that the overhead should be considered. Normally procedural statements have more control over what needs to be done while combination of functions tends to make code look neat. Which choice to go would depends on actual siturations. :)

Multiplication Aggregate

Hi,
Is there an equivalent to the SUM() aggregate function but instead of doing
a summation, I'd like to do a mulitiplication. Is there a way to achieve this?
Please help. Thanks!Sorry, no such thing as SUMPRODUCT() in SQL Server as there is in Excel.
GeoSynch
"ddinulos" <ddinulos@.discussions.microsoft.com> wrote in message
news:AF5F584D-A464-4658-901A-F435A7246B19@.microsoft.com...
> Hi,
> Is there an equivalent to the SUM() aggregate function but instead of doing
> a summation, I'd like to do a mulitiplication. Is there a way to achieve this?
> Please help. Thanks!
>