----- Original Message ----- From: "Leonardus Setiabudi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, August 22, 2004 11:45 PM Subject: Function to multiply multiple row values
> Hi All, > > Can someone point me a function that return the multiplication of query result.. > Hack.. It's hard for me just to explain what I nedd.. > > Ok, it goes like the sum() function, but instead of the summary.. It > will return the multiplication Here is what I need it to do > > x > ------- > 1 > 2 > 3 > 4 > > Select sum(x) would return 10 right.. > > Now I need a function .. Say fx() so that when I issued > > Select fx(x) ... > It would return 24 which is 1x2x3x4 > I assume that the function that yielded 24 was fx(4), not fx(x). In high school math, which was a *long* time ago for me, this was called a factorial function and was written like this: 4! = 1x2x3x4 You would say it out loud as "4 factorial equals 1 times 2 times 3 times 4". > Is there any function like that in mysql?? > I know I can do this with stored procedure.. But I'm using 4.1.3 > You're talking about two different functions: 1. The first one you described does exactly what sum() does (for integers) except that it multiplies instead of adds. Its input is an entire column (or, if there is a WHERE clause, only rows that satisfy the WHERE clause are considered, then their contents are multiplied together.) This function would be called a column function since it uses all or part of a column as its input argument. 2. The second one takes a single argument and determines the factorial result for it. It doesn't need even one row of a table; you simply supply an integer as the argument and determine that integer's factorial result. The function would be called a scalar function since it uses only a single value and transforms it in some way. Which one do you want? I suspect you want the first one, not the second one. I have not looked to see if the first one exists anywhere but I'd be surprised if it did; I've never seen that function in 20 years of working with relational databases. I'm not even sure if it would be possible to write your own column function in MySQL 4.1.3. I understand that user-written functions will eventually become available in 4.1.x but I'm not sure if they are there yet in 4.1.3. Have a look through the manual and see if it is possible to create a user-written column function in 4.1.3; if it is, you should be in business, assuming you can find the rules and techniques for creating custom column functions. Otherwise, I'm really not sure what you could do aside from writing your own program that does the equivalent work. I'm curious: why do you need to multiply all the values in a column together? I've never seen a business need for something like that. I can't imagine a real-world requirement for multiplying all the values in a column together. Or are you just doing some kind of school assignment? By the way, if you actually mean that you want to do the second function, the calculation of the factorial result for a single integer argument, that should be quite easy to write. I use DB2 a lot more than MySQL and I feel sure I could write that function in DB2 in very little time. Again, I'm not sure if 4.1.3 supports user-written scalar functions yet; if it doesn't you may have to wait a bit before creating this function. Can someone more familiar with the state of user-written functions in 4.1.3 please tell us exactly what can and can't be done with these at this point in time? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
