Mega Code Archive
Wrap case when into sum() function
<![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> select
2 sum ( case when salary between 6 and 10 then 1 else 0 end )
3 as sal_6_10,
4 sum ( case when salary between 11 and 15 then 1 else 0 end )
5 as sal_11_15,
6 sum ( case when salary between 16 and 20 then 1 else 0 end )
7 as sal_16_20,
8 sum ( case when salary between 21 and 25 then 1 else 0 end )
9 as sal_21_25
10 from emp;
SAL_6_10 SAL_11_15 SAL_16_20 SAL_21_25
---------- ---------- ---------- ----------
0 0 0 0
1 row selected.
SQL> drop table emp;
Table dropped.
]]>