|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Employee(s) Getting Less Than the Average Salary [message #608718 is a reply to message #608715] |
Sun, 23 February 2014 14:37   |
sonudev
Messages: 26
Registered: November 2009
Location: bangalore
|
Junior Member |
|
|
okay, then if I will do this like:
select ename,deptno,sal from emp where sal<(select avg(sal) from emp where deptno=e.deptno);
then the output comes as below.
EMPNO ENAME DEPTNO SAL
------ ---------- ---------- ----------
7369 SMITH 20 800
7521 WARD 30 1250
7654 MARTIN 30 1250
7782 CLARK 10 2450
7844 TURNER 30 1500
7876 ADAMS 20 1100
7900 JAMES 30 950
7934 MILLER 10 1300
But I want something different. Blackswan, can you please throw some more light on how to restructure the query for getting the output?
[Updated on: Sun, 23 February 2014 14:43] Report message to a moderator |
|
|
|
| Re: Employee(s) Getting Less Than the Average Salary [message #608719 is a reply to message #608718] |
Sun, 23 February 2014 14:45 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I don't think another way will be more efficient but here's one:
SQL> with
2 data as (
3 select empno, ename, deptno, sal,
4 avg(sal) over (partition by deptno) avgsal
5 from emp
6 )
7 select empno, ename, deptno, sal
8 from data
9 where sal < avgsal
10 /
EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7782 CLARK 10 2450
7934 MILLER 10 1300
7876 ADAMS 20 1100
7369 SMITH 20 800
7521 WARD 30 1250
7844 TURNER 30 1500
7900 JAMES 30 950
7654 MARTIN 30 1250
8 rows selected.
|
|
|
|
|
|
| Re: Employee(s) Getting Less Than the Average Salary [message #608721 is a reply to message #608720] |
Sun, 23 February 2014 15:13 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Sun, 23 February 2014 15:48
A more efficient way for your query would be:
And why do you think this will be more efficient than analytic function?
SQL> explain plan for
2 with avgsal as (
3 select empno,
4 ename,
5 deptno,
6 sal,
7 avg(sal) over(partition by deptno) avgsal
8 from emp
9 )
10 select empno,
11 ename,
12 deptno,
13 sal
14 from avgsal
15 where sal < avgsal
16 /
Explained.
SQL> select *
2 from table(dbms_xplan.display)
3 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4130734685
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 826 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 826 | 3 (0)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 238 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"<"AVGSAL")
15 rows selected.
SQL> explain plan for
2 with avgsal as (
3 select deptno,
4 avg(sal) avgsal
5 from emp
6 group by deptno
7 )
8 select empno,
9 ename,
10 emp.deptno,
11 sal
12 from emp,
13 avgsal
14 where emp.deptno = avgsal.deptno
15 and sal < avgsal
16 /
Explained.
SQL> select *
2 from table(dbms_xplan.display)
3 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 269884559
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 43 | 6 (0)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 3 (0)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="AVGSAL"."DEPTNO")
filter("SAL"<"AVGSAL")
18 rows selected.
SQL>
SY.
|
|
|
|
|
|
| Re: Employee(s) Getting Less Than the Average Salary [message #608765 is a reply to message #608730] |
Mon, 24 February 2014 08:48 |
sonudev
Messages: 26
Registered: November 2009
Location: bangalore
|
Junior Member |
|
|
Nice input Michel and Solomon also. The intended result looks good both ways. However, what if I try with the below query:
select empno,ename,deptno,sal from emp inner join (select avg(sal) as avgsal from emp) on sal<avgsal order by deptno)
EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7934 MILLER 10 1300
7876 ADAMS 20 1100
7369 SMITH 20 800
7900 JAMES 30 950
7844 TURNER 30 1500
7521 WARD 30 1250
7499 ALLEN 30 1600
7654 MARTIN 30 1250
8 rows selected.
;
However, the result is different. What I am missing here?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
