Create a query that will display the total no.of employees and, of that total, the no.of employees hired in 1995,1996,1997, and 1998. create appropriate column headings.

Questions by adarsh_sp   answers by adarsh_sp

Showing Answers 1 - 24 of 24 Answers

select Count(*) as NumberOfEmployees,

EmployeesHiredIn1995_1996_1997_1998 =

(select count(*) from employees

where hiredate like '%1995%'

or hiredate like '%1996%'

or hiredate like '%1997%'

or hiredate like '%1998%')

from employees

  Was this answer useful?  Yes

Sneha Atre

  • Oct 11th, 2006
 

select count(*)

from employees

where hiredate in(1995,1996,1997,1998);

Manjunath

  • Oct 19th, 2006
 

Check this......

select count(*) Count, to_char(hiredate, 'YYYY') YEAR from emp group by rollup( to_char(hiredate, 'YYYY'))

  Was this answer useful?  Yes

Sachin

  • Oct 19th, 2006
 

Select

count(emp_code),

count(case when TO_NUMBER(to_CHAR(hire_date,'YYYY'))=1995 then (emp_code) end) as hire_1995

count(case when TO_NUMBER(to_CHAR(hire_date,'YYYY'))=1996 then (emp_code) end) as hire_1996

from employees

--similary the query will continue. remember that the fields are from one table and you are not doing any dimension querying. only the fact info is being queried

  Was this answer useful?  Yes

select

count(decode(to_char(hiredate, 'yyyy') , '1995', hiredate, null)) hired_in_1995,
count(decode(to_char(hiredate, 'yyyy') , '1996', hiredate, null)) hired_in_1996,

count(decode(to_char(hiredate, 'yyyy') , '1997', hiredate, null)) hired_in_1997,

count(decode(to_char(hiredate, 'yyyy') , '1998', hiredate, null)) hired_in_1998,
count(*) total_emp
from emp;

  Was this answer useful?  Yes

Anil Patra

  • Dec 10th, 2006
 

Check this out.

select to_char(PADATEOFJOININGD,'YYYY'), count(*)

from PAEMPLOYEE_M

group by (to_char(PADATEOFJOININGD,'YYYY'))

union

select 'total emplyee', count(*)

from PAEMPLOYEE_M

 

  Was this answer useful?  Yes

baridbej

  • May 24th, 2008
 

SELECT COUNT(*) TOTAL_EMP,
SUM(CASE WHEN INSTR(HIREDATE,'81') > 0 THEN 1 ELSE 0 END)HIRED_81,
SUM(CASE WHEN INSTR(HIREDATE,'80') > 0 THEN 1 ELSE 0 END)HIRED_80,
SUM(CASE WHEN INSTR(HIREDATE,'87') > 0 THEN 1 ELSE 0 END)HIRED_87,
SUM(CASE WHEN INSTR(HIREDATE,'82') > 0 THEN 1 ELSE 0 END)HIRED_82
FROM EMP
/

This query is as per ORACLE 9I EMP table that I have in my home.

  Was this answer useful?  Yes

lanka_satya

  • Sep 12th, 2008
 

select count(*) Count, to_char(hiredate, 'YYYY') YEAR from emp group by to_char(hiredate, 'YYYY') having substr(to_char(hiredate,'yyyy'),-1) in(5,6,7,8)

  Was this answer useful?  Yes

javedans

  • Jul 8th, 2009
 

Please check with this query...
SELECT COUNT(EMPID) TOTAL_EMPS,
       DECODE(TO_CHAR(HIRE_DATE,YYYY)),'1995',COUNT(EMPID)) TOTAL_HIRE_1995,
       DECODE(TO_CHAR(HIRE_DATE,YYYY)),'1996',COUNT(EMPID)) TOTAL_HIRE_1996,
       DECODE(TO_CHAR(HIRE_DATE,YYYY)),'1997',COUNT(EMPID)) TOTAL_HIRE_1997,
       DECODE(TO_CHAR(HIRE_DATE,YYYY)),'1998',COUNT(EMPID)) TOTAL_HIRE_1998
FROM EMP

emraan

  • Dec 28th, 2009
 

select count(*), count(decode(to_char(hiredate, 'yyyy'), 1995,1)), 
                count(decode(to_char(hiredate, 'yyyy'), 1996,1)),
                count(decode(to_char(hiredate, 'yyyy'), 1997,1)),
                count(decode(to_char(hiredate, 'yyyy'), 1998,1)) from emp

Venkadesh Raja

  • Jan 19th, 2012
 

Code
  1. SELECT hiredate,count(*) FROM ven_emp

  2. WHERE hiredate BETWEEN 01-jan-95 AND 31-dec-98

  3. GROUP BY hiredate

  Was this answer useful?  Yes

P.R.KHUNTIA

  • Mar 19th, 2012
 

Code
  1. SELECT deptno,count(to_char(hiredate,yyyy),1980,empno) AS yr1980,

  2.                     count(to_char(hiredate,yyyy),1981,empno) AS yr1981,

  3.                     count(to_char(hiredate,yyyy),1982,empno) AS yr1982

  4.            FROM emp

  5.                     GROUP BY deptno;

  6.  

  Was this answer useful?  Yes

Sudarsan

  • Mar 14th, 2013
 

SELECT Hiredate,Count(*)
FROM emp
WHERE to_char(hiredate,yyyy) in (1995,1996,1997,1998)
GROUP BY Hiredate;

  Was this answer useful?  Yes

D.Venkat

  • Oct 31st, 2013
 

SELECT to_char(hiredate,YYYY),count(*) FROM employee GROUP BY to_char(hiredate,YYYY);

  Was this answer useful?  Yes

man singh

  • Feb 15th, 2015
 

Code
  1. SELECT to_char(hiredate,yyyy),count(*)

  2. FROM emp

  3. GROUP BY to_char(hiredate,yyyy)

  4. HAVING to_char(hiredate,yyyy) IN (1995,1996,1997,1998);


YaserAbbasi

  • Sep 25th, 2015
 

Late by couple of years but here you go

Code
  1. SELECT COUNT(*) AS "Total" ,SUM(CASE TO_CHAR(HIRE_DATE,YYYY) WHEN 1995 THEN 1 ELSE 0 END) AS "1995",

  2. SUM(CASE TO_CHAR(HIRE_DATE,YYYY) WHEN 1996 THEN 1 ELSE 0 END) AS "1996" ,

  3. SUM(CASE TO_CHAR(HIRE_DATE,YYYY) WHEN 1997 THEN 1 ELSE 0 END)  AS "1997",

  4. SUM(CASE TO_CHAR(HIRE_DATE,YYYY) WHEN 1998 THEN 1 ELSE 0 END)  AS "1998"

  5.  FROM HR.EMPLOYEES

  Was this answer useful?  Yes

RAVI

  • Dec 8th, 2015
 

Code
  1. SELECT TO_CHAR(HIREDATE,YYYY),COUNT(*) FROM EMP GROUP BY TO_CHAR(HIREDATE,YYYY) HAVING TO_CHAR(HIREDATE,YYYY) IN(1980,1981,1982);

  Was this answer useful?  Yes

Ashish

  • Apr 30th, 2016
 

This query we can write many ways, I will give two solution
1. By using UNION ALL operator
2. By using sub query

Code
  1. SELECT COUNT(*) AS TOTAL FROM EMPLOYEE

  2. UNION ALL

  3. SELECT COUNT(*) AS T FROM EMPLOYEE WHERE TO_CHAR(HIREDATE,YYYY) IN (1995,1996,1997,1998);

  4.  

  5.  

  6. SELECT HIRE1+HIRE2+HIRE3+HIRE4 AS HIRE ,TOTALEMP

  7.  FROM (SELECT

  8. COUNT(DECODE(TO_CHAR(hiredate, yyyy) , 1995, hiredate, NULL)) AS HIRE1,

  9. COUNT(DECODE(TO_CHAR(hiredate, yyyy) , 1996, hiredate, NULL)) AS HIRE2,

  10. COUNT(DECODE(TO_CHAR(hiredate, yyyy)  , 1997, hiredate, NULL)) AS HIRE3,

  11. COUNT(DECODE(TO_CHAR(hiredate, yyyy)  , 1998, hiredate, NULL)) AS HIRE4,

  12. COUNT(*) AS TOTALEMP

  13. FROM EMPLOYEE);

  Was this answer useful?  Yes

kinza

  • Oct 21st, 2016
 

What if we want to display list of salaries per year in last 20 years?

  Was this answer useful?  Yes

Mercy

  • Oct 29th, 2018
 

Hope the below query will answer the question.

Code
  1. SELECT COUNT(*) total,

  2. SUM(decode(to_char(hire_date,yyyy),1995,1,0)) "1995",

  3. SUM(decode(to_char(hire_date,yyyy),1996,1,0)) "1996",

  4. SUM(decode(to_char(hire_date,yyyy),1997,1,0)) "1997",

  5. SUM(decode(to_char(hire_date,yyyy),1998,1,0)) "1998"

  6. FROM employees

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions