Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: 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?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Maybe you can show what formula you want to apply?
    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.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ok, how about:

    Code:
    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
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You mean like:

    Code:
    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....
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mmm, i love overflow

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


  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...

    [dooh]
    Noooow I get it...
    [/dooh]


    Great episode...

    Got to pick me up some cough medice on the way home...
    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.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    a MULT function for varchar?

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

    What exactly are you trying to do?
    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.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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)))

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    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.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    That appears to be returning null
    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


  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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
    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?

Posting Permissions

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