Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Question Unanswered: 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!
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Nevermind...found it...

    Got it...
    Code:
    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...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What, did nobody pay attention during High School math classes?

    select POWER(10, sum(log10(MyFloat))) from MyTable
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I have a harder time believing he has a freind....



    Just joking of course....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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

    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
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Talking Is that your final answer???

    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:

    Code:
    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...
    Last edited by TallCowboy0614; 06-17-04 at 18:22. Reason: 'cause it really WAS for a friend (OK, a COWORKER anyway)
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Hi, this is Paul's boss...thanks for exploding his head...

    Now..WHO is going to cleanup this mess?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, if you'll check out my title I AM the Electronic Janitor.

    Now where did I put my Electro-mop...?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •