Mega Code Archive
DECODE in the GROUP BY clause
<![CDATA[
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> create or replace view avg_Sal
2 as select trunc(avg(salary)) avg_sal
3 from emp
4 /
View created.
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.
]]>