> > 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". > > 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?
Hello there Rhino.. thanks for the quick response and yes you're right.. it's the first function description that i need not that factorial kind of thing.. the application for the real business world.. well maybe it's just me or my table design.. say, you have a table 'product' and 'product_unit' create table product(product_id char(10), product_name char(20)); create table product_unit(product_id char(10), unit_id char(5), unit_conversion integer); ps: lets just ignore indexes this time product : product_id product_name --------------- --------------------- ITEM01 SOME ITEM ITEM02 OTHER ITEM product_unit: product_id unit_id unit_conversion --------------- --------- ----------------------- ITEM01 BOX 12 ITEM01 PIECE 1 ITEM02 BOX 20 ITEM02 CARTON 12 ITEM02 BOTTLE 1 and it reads as follow ITEM01 have 2 units of measure, BOX and PIECE, each BOX contain 12 PIECE ITEM02 have 3 units of measure, BOX, CARTON and BOTTLE in each BOX consist of 20 CARTON, and each CARTON contain 12 BOTTLE lets say i have 5 BOX of ITEM02, how much is it in BOTTLE??? it's should be 5 x (20 x 12) = 1200 BOTTLEs and i need something like select FX(unit_conversion) from product_unit where product_id="ITEM01" and unit_id="BOTTLE" which then should result 240 so you can see.. this is not a school assignment :) it's real.. especially if you work in a manufacturing or distribution company.. yes, this problem can be solved with temporary tables, or even stored procedure in 5.x and definitly on client side programming (just loop and multiply it) but it will help to simplify the query a LOT if theres somekind of function Thanks :)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
