Product of Salary

[Bernhard Hiller]

There is a trick, see http://www.sql-server-performance.com/tips/user_defined_functions_p1.aspx[^]. But somehow your example looks like homework, the product of salaries is just nonsense...

[Lost User]

This Is Just for Knowing Concept to How To Multiply Fields Values. And I Say that i do't want to use PL/SQL.

If you can think then I Can.

[Jorgen Andersson]

You can use select exp(sum(ln(sal))) from emp which of course will throw an exception for negative numbers and zero. But that you can easily fix with a case expression. The best solution is of course to create a function to do the job.

List of common misconceptions

[Lost User]

Can u please explain with the form of SQL Query.

If you can think then I Can.

[Jorgen Andersson]

eg_Anubhava wrote:

Can u please explain with the form of SQL Query.

Well I did. I'll do it again, with extra explanation, and I'll write slowly. You have a table EMP that has a column SAL that you want the product from instead of the sum. Then the query can look like this:

SELECT EXP(SUM(LN(SAL)))
FROM EMP

This query has the drawback that the LN() function will throw an exception for negative numbers and zero. Tip: CASE WHEN and SIGN() will be useful. This can be mathematically written like: A*B = eln(A)+ln(B)

List of common misconceptions

[Bernhard Hiller]

eg_Anubhava wrote:

Can u please explain with the form of SQL Query.

which proves that you did not look at the link I posted above. It is shown there!

[Lost User]

It is generating an Error while executing this Query. Error Message is : Undefined Function 'LN' in Expression.

If you can think then I Can.

[Pete OHanlon]

If you don't want to use any of the built in Oracle commands, then you could achieve this by using a cursor to iterate over the user's salary and keep a running total that way.

I'm not a stalker, I just know things. Oh by the way, you're out of milk.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Onyx

[Jorgen Andersson]

ln() is supported on Oracle since version 8i. If you're using an older version than that, you're having bigger problems than the missing ln() function. But if you by any chance aren't using Oracle despite your numerous references to PL/SQL, then both SqlServer and MSAccess uses log() instead of ln().

List of common misconceptions

[Jorgen Andersson]

And obviously I didn't either. :doh:

List of common misconceptions

[RyanEK]

declare @temp table (ename varchar(10), sal int)
insert into @temp values ('Anu', 10)
insert into @temp values ('Minu', 20)
insert into @temp values ('Tusar', 20)
insert into @temp values ('Piter', 10)

select exp(sum(log(sal))) from @temp

[Lost User]

It returns the wrong answer in some cases change the values and then try. the difference of values are in some points.

If you can think then I Can.

[RyanEK]

Try

SELECT CAST(ROUND(COALESCE(EXP(SUM(LOG(ABS(NULLIF(sal,0))))),0)*SIGN(MIN( ABS(sal)))*(COUNT(NULLIF(SIGN(sal),1))%2*-2+1),0) AS INTEGER)
FROM @temp

[Lost User]

Briallent Wow Thanks

If you can think then I Can.

[Wendelius]

Hi, Even though you mentioned that you don't want to use PL/SQL you could be interested in this: Custom aggregates in Oracle[^] Best regards, mika

The need to optimize rises from a bad design.My articles[^]