View previous topic :: View next topic
|
Author |
Message |
murali_andaluri Currently Banned New User
Joined: 07 May 2005 Posts: 22
|
|
|
|
Hi friends,
please solve this query...
Select the AvgSalaryDifference for all employees
Employee: ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ----------------------------------------------------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N |
|
Back to top |
|
|
nuthan
Active User
Joined: 26 Sep 2005 Posts: 146 Location: Bangalore
|
|
|
|
Hi Murali,
Can you please explain clearly.. |
|
Back to top |
|
|
murali_andaluri Currently Banned New User
Joined: 07 May 2005 Posts: 22
|
|
|
|
Hi Nutan,
first we need to take all salary differences and then we have to take those salary differences average.
regards
murali |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
first we need to take all salary differences and then we have to take those salary differences average. |
What "thing" are the salaries "different" from? Some literal, some field, somethng else?
When you more clearly define what you need, someone should be able to offer a suggestion.
As often happens, your question is completely clear to you, but it is not to everyone who reads the post. |
|
Back to top |
|
|
murali_andaluri Currently Banned New User
Joined: 07 May 2005 Posts: 22
|
|
|
|
Hi,
we need to take salary differences between every employee and then calculate the avg for all differences.
regards
murali |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
I guess You mean for each row the difference from the average for all rows??
I hope that this is an homework,
otherwise I would be more comfortable with a more,
theoretically sound, statistical approach
Why don' t You take a look at the statistical functions of DB2 ?? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
What does this
Quote: |
between every employee |
mean? If there are 100 employees, there will be 99 differences (some may contain the same value). It seems more likely that the requirement is to find the difference between someone's salary and the average of all salaries, rather than what has been posted. . .
It will probably help if you show 10 employees, their salary, and what you want for output when they are processed. |
|
Back to top |
|
|
neeleshreddy
New User
Joined: 24 Aug 2006 Posts: 4 Location: Hyd
|
|
|
|
Here is the required query...
SELECT AVG(A.SALARY-B.SALARY) FROM EMPLOYEE23 A,
EMPLOYEE23 B WHERE A.SALARY > B.SALARY AND A.EMP_ID <>
B.EMP_ID; |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello Neelesh and welcome to the forums,
Until the requirement definition is posted, i'm not sure we can say
Quote: |
Here is the required query... |
It may be the required query, but there is no way to know until the "real" rules are posted.
The info from Murali is unclear at best. |
|
Back to top |
|
|
murali_andaluri Currently Banned New User
Joined: 07 May 2005 Posts: 22
|
|
|
|
Hi Dick,
you are correct the requirement is to find the difference between someone's salary and the average of all salaries....
so now i hope that the reuirement is clear for all. |
|
Back to top |
|
|
neeleshreddy
New User
Joined: 24 Aug 2006 Posts: 4 Location: Hyd
|
|
|
|
Hi Murali
In that case this would be the required query
SELECT (SALARY-(SELECT AVG(SALARY) FROM EMPLOYEE23)) FROM
EMPLOYEE23;
Always fun at work
Neelesh. |
|
Back to top |
|
|
snehalpatel
New User
Joined: 13 Sep 2007 Posts: 37 Location: India
|
|
|
|
murali_andaluri wrote: |
Hi friends,
please solve this query...
Select the AvgSalaryDifference for all employees
Employee: ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ----------------------------------------------------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 New York N |
Suppose u have below data, and If you use Neelesh query then you get below result
Code: |
If there are 4 employee having salary as
EMP NO 1 100
EMP NO 2 200
EMP NO 3 300
EMP NO 4 400
Then We will get avg sal as 250
If we run the above query we will get results as
EMP NO 1 -150
EMP NO 2 -50
EMP NO 3 50
EMP NO 4 150 |
You can use above query if you have similar requirement. |
|
Back to top |
|
|
|