Product of Salary
-
You can use
select exp(sum(ln(sal))) from empwhich 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. -
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
EMPthat has a columnSALthat you want the product from instead of the sum. Then the query can look like this:SELECT EXP(SUM(LN(SAL)))
FROM EMPThis query has the drawback that the
LN()function will throw an exception for negative numbers and zero. Tip:CASE WHENandSIGN()will be useful. This can be mathematically written like: A*B = eln(A)+ln(B) -
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!
-
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
EMPthat has a columnSALthat you want the product from instead of the sum. Then the query can look like this:SELECT EXP(SUM(LN(SAL)))
FROM EMPThis query has the drawback that the
LN()function will throw an exception for negative numbers and zero. Tip:CASE WHENandSIGN()will be useful. This can be mathematically written like: A*B = eln(A)+ln(B) -
Hello Everybody, As we find the Sum of Salary from Emp Table. SQL Query : Select Sum(Emp) from Emp; Now I want to get the product of Salary : Product means (first sala * Second Sal * Third Salary * ...... * Last salary); How can i use with the help of SQL Command. I Do't want to USE PL/SQL Command. Thanks
If you can think then I Can.
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
-
It is generating an Error while executing this Query. Error Message is : Undefined Function 'LN' in Expression.
If you can think then I Can.
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().
-
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!
And obviously I didn't either. :doh:
-
Hello Everybody, As we find the Sum of Salary from Emp Table. SQL Query : Select Sum(Emp) from Emp; Now I want to get the product of Salary : Product means (first sala * Second Sal * Third Salary * ...... * Last salary); How can i use with the help of SQL Command. I Do't want to USE PL/SQL Command. Thanks
If you can think then I Can.
-
-
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.
-
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 -
Hello Everybody, As we find the Sum of Salary from Emp Table. SQL Query : Select Sum(Emp) from Emp; Now I want to get the product of Salary : Product means (first sala * Second Sal * Third Salary * ...... * Last salary); How can i use with the help of SQL Command. I Do't want to USE PL/SQL Command. Thanks
If you can think then I Can.
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[^]