DECODE in the GROUP BY clause : DECODE « Conversion Functions « Oracle PL / SQL

1. Oracle PL / SQL
2. Conversion Functions
3. DECODE

DECODE in the GROUP BY clause

   

SQL>
SQL> create table emp(
  2           emp_no                 integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,shortZipCode           varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,salary                 number(5,2)
 14          ,birthdate              date
 15          ,startDate              date
 16          ,title                  varchar2(20)
 17          ,dept_no                integer
 18          ,mgr                    integer
 19          ,region                 number
 20          ,division               number
 21          ,total_sales            number
 22  );

Table created.

SQL>
SQL> -- emp Table Inserts:
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values
  2                      (1,'Z','Joy','R','1 Ave','New York','NY','12122','2333','212','200-1111','12-nov-1976','President');

1 row created.

SQL>
SQL>
SQL> create or replace view avg_Sal
  2  as select trunc(avg(salary)) avg_sal
  3  from emp
  4  /

View created.

SQL>
SQL>
SQL> select
  2  decode(  sign( (salary - avg_sal ) ),
  3       1, '> Average of ' || to_char(avg_sal, '99.99') ,
  4       0, '= Average of ' || to_char(avg_sal, '99.99'),
  5          -1, '< Average of ' || to_char(avg_sal, '99.99')  ) sal_desc,
  6   count(*)
  7  from emp, avg_sal
  8  group by
  9  decode(  sign( (salary - avg_sal ) ),
 10       1, '> Average of ' || to_char(avg_sal, '99.99') ,
 11       0, '= Average of ' || to_char(avg_sal, '99.99'),
 12          -1, '< Average of ' || to_char(avg_sal, '99.99')  )
 13  /

                             1

1 row selected.

SQL>
SQL> drop table emp;

Table dropped.

   
    
    
  

Related examples in the same category