Oracle SQL - Compare with average

Introduction

Consider the following sql. It is trying to get who earns more than the average salary?

select empno
from   emp
where  msal > avg(msal);

You would get the following error message:

where  msal > avg(msal)
                        *
ERROR at line 3:
ORA-00934: group function is not allowed here

WHERE clause has only a single row as its context, you cannot use the AVG function here.

The following code shows one way to Find Who Earns More Than the Average Salary: use sub-query to calculate average value.

select e.empno
from   emp e
where  e.msal > (select avg(x.msal)
                 from   emp x );

Another Way to Find Who Earns More Than the Average Salary is to use the

The following script is based on the Cartesian product of the EMPLOYEES table with itself.

It doesn't have a WHERE clause and it groups on e1.EMPNO and e1.MSAL, which allows you to refer to this column in the HAVING clause.

Demo

SQL>
SQL>-- w  w  w. jav a2  s .c om
SQL> drop table emp;

Table dropped.

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL> select   e1.empno
  2  from     emp e1
  3  ,        emp e2
  4  group by e1.empno
  5  ,        e1.msal
  6  having   e1.msal > avg(e2.msal);

    EMPNO
---------
 07009.00
 07008.00
 07004.00
 07012.00
 07006.00
 07902.00

6 rows selected.

SQL>

Oracle SQL - Compare with average

Introduction

Demo

Related Topic