Home » SQL & PL/SQL » SQL & PL/SQL » how to wite this query?
how to wite this query? [message #230696] Thu, 12 April 2007 10:49 Go to next message
oll3i
Messages: 9
Registered: April 2007
Location: Poland
Junior Member
write a query which determins for each emplyee average salary for those employees who have salaries 100 $ greater or 100$ lower than the salary of that employee and which work in the same department
Re: how to wite this query? [message #230706 is a reply to message #230696] Thu, 12 April 2007 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Study analytic function avg over.

Regards
Michel
Re: how to wite this query? [message #230715 is a reply to message #230696] Thu, 12 April 2007 11:21 Go to previous messageGo to next message
oll3i
Messages: 9
Registered: April 2007
Location: Poland
Junior Member
but cd you give me some hint?
Re: how to wite this query? [message #230720 is a reply to message #230696] Thu, 12 April 2007 11:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>cd you give me some hint?
-bash-3.00$ cd you
-bash: cd: you: No such file or directory

Or would you prefer us (TINU) to provide you the complete SQL statement for this homework assignment?

P.S.
We do NOT speak IM in this forum.
Re: how to wite this query? [message #230725 is a reply to message #230696] Thu, 12 April 2007 11:52 Go to previous messageGo to next message
oll3i
Messages: 9
Registered: April 2007
Location: Poland
Junior Member
sorry but the lecture doesnt explain much and i even dont know how to start
shd i use range?

RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING
Re: how to wite this query? [message #230727 is a reply to message #230725] Thu, 12 April 2007 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correct.
Go on, you almost have the query.

Regards
Michel

[Updated on: Thu, 12 April 2007 11:57]

Report message to a moderator

Re: how to wite this query? [message #230732 is a reply to message #230696] Thu, 12 April 2007 12:34 Go to previous messageGo to next message
oll3i
Messages: 9
Registered: April 2007
Location: Poland
Junior Member
but that's all i know that i have to use range

wd it be sth like that

SELECT e.name,d.department,
AVG(e.salary) OVER RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING from
employees e,department d
where e.id_departament=d.id_department
Re: how to wite this query? [message #230737 is a reply to message #230732] Thu, 12 April 2007 12:40 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
oll3i wrote on Thu, 12 April 2007 13:34

wd it be sth like that



Invalid syntax.
Re: how to wite this query? [message #230738 is a reply to message #230732] Thu, 12 April 2007 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read again the syntax and examples.

Another point, I don't see the usefulness of dept.

Regards
Michel

Re: how to wite this query? [message #230740 is a reply to message #230696] Thu, 12 April 2007 12:41 Go to previous messageGo to next message
oll3i
Messages: 9
Registered: April 2007
Location: Poland
Junior Member
so what will be the right syntax?
Re: how to wite this query? [message #230742 is a reply to message #230740] Thu, 12 April 2007 12:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
"would it be something like that"
Re: how to wite this query? [message #230743 is a reply to message #230742] Thu, 12 April 2007 12:43 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I really did not know what sth was. I though he/she had turned to the Dark Side.
Re: how to wite this query? [message #230745 is a reply to message #230740] Thu, 12 April 2007 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Kasia, it is in the link I posted.

Regards
Michel

Re: how to wite this query? [message #230756 is a reply to message #230696] Thu, 12 April 2007 13:22 Go to previous messageGo to next message
oll3i
Messages: 9
Registered: April 2007
Location: Poland
Junior Member
SELECT e.name,d.department,
AVG(e.salary) OVER (ORDER BY e.salary RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING) from
employees e,department d
where e.id_departament=d.id_department
Re: how to wite this query? [message #230758 is a reply to message #230756] Thu, 12 April 2007 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Close.

department table is useless (unless you want the name of department).
But your requirement is
Quote:
which work in the same department

Regards
Michel
Re: how to wite this query? [message #230885 is a reply to message #230696] Fri, 13 April 2007 03:50 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I've got quite curious about this, because I've not been able to get this to work. Maybe someone could enlighten me what I'm doing wrong. If I have data:

create table emp (empno number, deptno number, salary number);

insert into emp values (1, 1, 250);
insert into emp values (2, 1, 350);
insert into emp values (3, 1, 450);
insert into emp values (4, 1, 550);
insert into emp values (5, 1, 650);

The correct answer, as computed using an inline view would be:

select empno, (select avg(salary) from emp e1 where e1.deptno=e.deptno and e1.salary between e.salary-100 and e.salary+100)
from emp e

EMPNO     AVG                                    
1         300                                    
2         350                                    
3         450                                    
4         550                                    
5         600      

If I try it with an analytic function, I get something completely different:

SELECT empno,
avg(salary) OVER (PARTITION BY deptno order by salary RANGE
BETWEEn salary-100 preceding AND salary+100 following) avg_sal,
count(salary) over (PARTITION BY deptno order by salary RANGE
BETWEEn (salary-100) preceding AND salary+100 following) count_sal
FROM emp

EMPNO     AVG_SAL     COUNT_SAL                              
1         400         4                                      
2         450         5                                      
3         450         5                                      
4         450         5                                      
5         450         5                                     

From the count, it looks as if (salary-100) and (salary+100) are just being treated as numbers of rows. If I change it look for salaries which are within 10% of the current salary, it seems to work correctly.

SELECT empno,
avg(salary) OVER (PARTITION BY deptno order by salary RANGE
BETWEEn salary*.1 preceding AND salary*.1 following) avg_sal,
count(salary) over (PARTITION BY deptno order by salary RANGE
BETWEEn (salary*.1) preceding AND salary*.1 following) count_sal
FROM emp

EMPNO     AVG_SAL     COUNT_SAL                              
1         250         1                                      
2         350         1                                      
3         450         1                                      
4         550         1                                      
5         650         1       

There are no salaries with 10%, so the average is just the current salary. If it was interpreting it as a number or rows, 10% of 650 is 65 so you expect the last one to be the average of all 5 rows. Is there something you need to add to make sure Oracle treats the expression as SQL rather than just a row count ?




Re: how to wite this query? [message #230891 is a reply to message #230885] Fri, 13 April 2007 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Definition of range is:
Quote:
RANGE specifies the window as a logical offset.

(emphasis is mine).

Regards
Michel
Re: how to wite this query? [message #231006 is a reply to message #230891] Fri, 13 April 2007 12:31 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It is because you have a too large window. The order by salary implicitly takes the current salary, so you only need 100 preceding and 100 following. Not salary-100 or salary+100
 1  SELECT faqno
 2  ,      salary
 3  ,      avg(salary) OVER (PARTITION BY deptno order by salary
 4  range BETWEEn 100 preceding AND 100 following) avg_sal
 5  ,      count(salary) over (PARTITION BY deptno order by salary
 6  range BETWEEn 100 preceding AND 100 following) count_sal
 7* FROM faq
QL> /

    FAQNO     SALARY    AVG_SAL  COUNT_SAL
--------- ---------- ---------- ----------
        1        250        300          2
        2        350        350          3
        3        450        450          3
        4        550        550          3
        5        650        600          2


[Edit: added example]

[Updated on: Fri, 13 April 2007 12:32]

Report message to a moderator

Previous Topic: Count of error messages
Next Topic: deleteing duplicates using rownum
Goto Forum:
  


Current Time: Sun Dec 04 18:47:21 CST 2016

Total time taken to generate the page: 0.16008 seconds