Chain Singh

• Nov 22nd, 2007

 

The query is:

select count(*) as Totalnoofemployees, to_char(dateofjoining,'yyyy') as hiredyear from tablename
group by to_char(dateofjoining,'yyyy')

suganthi.m

 Profile Answers by suganthi.m 

• Jan 4th, 2008

 

select count(*) as Totalnoofemployees, to_char(dateofjoining,'yyyy') as hiredyear from tablename

dinu_26

 Profile Answers by dinu_26 

• Nov 12th, 2008

 

select count(*) as Totalnoofemployees, to_char(dateofjoining,'yyyy') as hiredyear from tablename

mahen.12

 Profile Answers by mahen.12 

• Jan 21st, 2009

 

I think this is the query you are looking for !

 select  
    count(*) ,  
    count(decode(to_char(hiredate,'YYYY'),'1980',hiredate) ) "1980" ,  
    count(decode(to_char(hiredate,'YYYY'),'1981',hiredate) ) "1981" , 
    count(decode(to_char(hiredate,'YYYY'),'1982',hiredate) ) "1982" ,
    count(decode(to_char(hiredate,'YYYY'),'1983',hiredate) ) "1983"  
 from emp ;

sqlnovice

 Profile Answers by sqlnovice 

• Aug 5th, 2009

 

I believe a small change to one of the answers already given here should give the desired results.

select count(*) as "Total Number of Employees",
count(decode(to_char(hiredate, 'YYYY'), '1980', empno) ) as "1980"
count(decode(to_char(hiredate, 'YYYY'), '1981', empno) ) as "1981"
count(decode(to_char(hiredate, 'YYYY'), '1982', empno) ) as "1982"
count(decode(to_char(hiredate, 'YYYY'), '1983', empno) ) as "1983"
from emp ;

vmvinod6

 Profile Answers by vmvinod6 

• Aug 6th, 2009

 

select
count(*) tital,
sum(decode(to_char(hire_date,'yyyy'),'1981',1,0)) "1981",
sum(decode(to_char(hire_date,'yyyy'),'1982',1,0)) "1982",
sum(decode(to_char(hire_date,'yyyy'),'1983',1,0)) "1983",
sum(decode(to_char(hire_date,'yyyy'),'1984',1,0)) "1984"
from employees;
 

gvmahesh

 Profile Answers by gvmahesh Questions by gvmahesh

• Aug 13th, 2009

 

SELECT count(*) FROM emp where TO_CHAR(hiredate,'YYYY') in (1980,1981,1982,1983);

letsconverse

 Profile Answers by letsconverse 

• Sep 21st, 2009

 

This one is better I guess:

query:

SELECT ((count(DECODE(to_char(hire_date,'YYYY'), 1995, 'c1')) )+(count(DECODE(to_char(hire_date,'YYYY'), 1996, 'c2')))+(count(DECODE(to_char(hire_date,'YYYY'), 1997, 'c3')))+(count(DECODE(to_char(hire_date,'YYYY'), 1998, 'c4')))) as TOTAL,
count(DECODE(to_char(hire_date,'YYYY'), 1995, 'c1')) "1995",
count(DECODE(to_char(hire_date,'YYYY'), 1996, 'c2')) "1996",
count(DECODE(to_char(hire_date,'YYYY'), 1997, 'c3')) "1997",
count(DECODE(to_char(hire_date,'YYYY'), 1998, 'c4')) "1998"
FROM employees;

Results for the above query is like:

     TOTAL       1995       1996       1997       1998
  ----------   ----------   ----------  ----------  ----------
          65             4            10           28           23

Revert if you have a question. I'll try.
NOTE: You may want to change the spl characters and the years based on your requiremnet.

Thanks

gvmahesh

 Profile Answers by gvmahesh Questions by gvmahesh

• Sep 23rd, 2009

 

using INLINE's

select ename,sal,job,e1.noofemployees from emp,
(select count(*) noofemployees from emp) e1 where to_char(hiredate,'YYYY') in (1980,1981,1982,1983);

using set operator:UNION

select nvl(to_char(null),'no.of employees') empdetails,
to_char(to_date(count(*),'j'),'jsp') jobsalongwithnoofemployees from emp
 union
 select ename,job from emp
 where to_char(hiredate,'YYYY') in (1980,1981,1982,1983);

chona

 Profile Answers by chona 

• Oct 9th, 2009

 

i think it's something like this...

SELECT year_start, COUNT (empid) total_count_per_year
  FROM (SELECT empid, TO_CHAR (TRUNC (hiredate, 'YYYY'), 'YYYY') year_start
          FROM emp
         WHERE TO_CHAR (TRUNC (hiredate, 'YYYY'), 'YYYY') IN
                     ('1980', '1981', '1982', '1983'))
GROUP BY startyear
UNION
SELECT 'TOTAL', SUM (total_count_per_year)
  FROM (SELECT year_start, COUNT (empid) total_count_per_year
          FROM (SELECT empid,
                       TO_CHAR (TRUNC (hiredate, 'YYYY'), 'YYYY') year_start
                  FROM emp
                 WHERE TO_CHAR (TRUNC (hiredate, 'YYYY'), 'YYYY') IN
                             ('1980', '1981', '1982', '1983'))
        GROUP BY startyear)

chona

 Profile Answers by chona 

• Oct 9th, 2009

 

or may be something like this:

select count(*) total,
sum(decode (to_char(hire_date, 'YYYY'), 1980, 1,0)) "1980",
sum(decode (to_char(hire_date, 'YYYY'), 1981, 1,0)) "1981",
sum(decode (to_char(hire_date, 'YYYY'), 1982, 1,0)) "1982",
sum(decode (to_char(hire_date, 'YYYY'), 1983, 1,0)) "1983"
from emp

ibeltc

 Profile Answers by ibeltc 

• Nov 5th, 2009

 

SELECT count(employee_id) "TOTAL",
count((CASE WHEN (hire_date >= '01-JAN-80') AND (hire_date < '1-JAN-81') THEN 1 END)) "1980",
count((CASE WHEN (hire_date >= '01-JAN-81') AND (hire_date < '1-JAN-82') THEN 1 END)) "1981",
count((CASE WHEN (hire_date >= '01-JAN-82') AND (hire_date < '1-JAN-83') THEN 1 END)) "1982",
count((CASE WHEN (hire_date >= '01-JAN-83') AND (hire_date < '1-JAN-84') THEN 1 END)) "1983"
FROM employees;

rubin2008

 Profile Answers by rubin2008 Questions by rubin2008

• Jan 31st, 2010

 

SELECT t. *
FROM (

SELECT eid
FROM employee
WHERE eyear = '1980'

) as t

muralikasya

 Profile Answers by muralikasya Questions by muralikasya

• Feb 9th, 2010

 

select count(*),hiredate  from emp where hiredate like '%198%'

anand.managoli

 Profile Answers by anand.managoli 

• Nov 26th, 2010

 

The query you are looking for is:

select (select count(*) from employee) as Total,(select count(*) from employee where hiredate>='1980-1-1' and hiredate<='1981-1-1') as Hired_1980, (select count(*) from employee where hiredate>='1981-1-1' and hiredate<='1982-1-1') as Hired_1981,

(select count(*) from employee where hiredate>='1982-1-1' and hiredate<='1983-1-1') as Hired_1982,(select count(*) from employee where hiredate>='1983-1-1' and hiredate<='1984-1-1') as Hired_1983;

kumarkqc

 Profile Answers by kumarkqc 

• Apr 24th, 2011

 

select count(*) from employees where Hired IN(1980,1981,1982,1983)

ashwini

• Jun 22nd, 2015

 

Can anybody tell me how to get the count of the test cases that was run by a person?

serine

• Aug 26th, 2016

 

Why did we use c1?