OraFAQ Forum: SQL & PL/SQL » quary displaying hiredates?

Home » SQL & PL/SQL » SQL & PL/SQL » quary displaying hiredates?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

quary displaying hiredates? [message #236012]

Tue, 08 May 2007 12:14

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

Report message to a moderator

Re: quary displaying hiredates? [message #236020 is a reply to message #236012]

Tue, 08 May 2007 13:07

Bill B
Messages: 1971
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

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	.xls to oracle table
Next Topic:	SQL * plus problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 05:41:09 CST 2024