Home » SQL & PL/SQL » SQL & PL/SQL » quary displaying hiredates?
quary displaying hiredates? [message #236012] Tue, 08 May 2007 12:14 Go to next message
Maple50175
Messages: 16
Registered: April 2007
Location: MA
Junior Member

I need to make a query that will display the total number of employees and of that total the number who were hired in 1980, 198l, l982, and 1983.


like below


TOTAL 1980 1981 1982 1983
----------- ------- -------- -------- ---------
14 1 10 2 1
Re: quary displaying hiredates? [message #236020 is a reply to message #236012] Tue, 08 May 2007 13:07 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select count(*) tot_emps,
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0) tot_1980,
sum(decode(to_char(hiredate,'YYYY'),'1981',1,0) tot_1981,
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0) tot_1982,
sum(decode(to_char(hiredate,'YYYY'),'1983',1,0) tot_1983
from emp;

[Updated on: Tue, 08 May 2007 13:34]

Report message to a moderator

Re: quary displaying hiredates? [message #236022 is a reply to message #236012] Tue, 08 May 2007 13:22 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select count(*) total,
  2         count(decode(extract(year from hiredate),1980,1)) "1980",
  3         count(decode(extract(year from hiredate),1981,1)) "1981",
  4         count(decode(extract(year from hiredate),1982,1)) "1982",
  5         count(decode(extract(year from hiredate),1983,1)) "1983"
  6  from emp
  7  /
     TOTAL       1980       1981       1982       1983
---------- ---------- ---------- ---------- ----------
        12          1         10          1          0

1 row selected.

Regards
Michel
Previous Topic: .xls to oracle table
Next Topic: SQL * plus problem
Goto Forum:
  


Current Time: Sat Dec 10 18:21:35 CST 2016

Total time taken to generate the page: 0.07443 seconds