CODEWITHSUNDEEP
sqloracle-databasefunctioncount
knatz
knatz

Reputation: 51

Beginner Oracle SQL Count Function Multiple columns

I am going through a practice worksheet with questions and expected outputs. The last question is proving to be difficult for me to grasp. Can anyone help please.

Question

Create a query to display the total number of employees and, of that total, the number of employees hired in 2005, 2006, 2007 and 2008.

Expected output format

Total 2005 2006 2007 2008
107   29   24   19   11

The following are my attempts to get the results with separate queries

TO_CHAR(hire_date,'YYYY')
SELECT COUNT(employee_id) AS "Total"
FROM employees;

SELECT COUNT(employee_id) AS "2005"
FROM employees
WHERE TO_CHAR(hire_date,'YYYY') LIKE '2005' GROUP BY TO_CHAR(hire_date,'YYYY') ;

SELECT COUNT(employee_id) AS "2006"
FROM employees
WHERE TO_CHAR(hire_date,'YYYY') LIKE '2006' GROUP BY TO_CHAR(hire_date,'YYYY') ;

SELECT COUNT(employee_id) AS "2007"
FROM employees
WHERE TO_CHAR(hire_date,'YYYY') LIKE '2007' GROUP BY TO_CHAR(hire_date,'YYYY') ;

SELECT COUNT(employee_id) AS "2008"
FROM employees
WHERE TO_CHAR(hire_date,'YYYY') LIKE '2008' GROUP BY TO_CHAR(hire_date,'YYYY') ;

Any help producing the result as one query is greatly appreciated.

Upvotes: 2

Views: 4611

Answers (2)

Taryn
Taryn

Reputation: 247860

You are trying to pivot the data, so you can use your existing query but add a CASE expression inside of the aggregate:

SELECT COUNT(employee_id) AS "Total",
    sum(case when TO_CHAR(hire_date,'YYYY') = '2005' then 1 else 0 end) "2005",
    sum(case when TO_CHAR(hire_date,'YYYY') = '2006' then 1 else 0 end) "2006",
    sum(case when TO_CHAR(hire_date,'YYYY') = '2007' then 1 else 0 end) "2007",
    sum(case when TO_CHAR(hire_date,'YYYY') = '2008' then 1 else 0 end) "2008"
FROM employees;

Depending on your version of Oracle, you might be able to use the PIVOT function:

select *
from
(
  select count(*) over() Total,
    TO_CHAR(hire_date,'YYYY') Year
  from employees
) 
pivot
(
    count(Year)
    for Year in ('2005', '2006', '2007', '2008')
)

See Demo of both queries

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

To put these in columns, use conditional aggreagtaion:

select count(*) as Total,
       sum(case when to_char(hire_date, 'yyyy') = '2005' then 1 else 0 end) as "2005",
       sum(case when to_char(hire_date, 'yyyy') = '2006' then 1 else 0 end) as "2006",
       sum(case when to_char(hire_date, 'yyyy') = '2007' then 1 else 0 end) as "2007",
       sum(case when to_char(hire_date, 'yyyy') = '2008' then 1 else 0 end) as "2008"
from employees
where to_char(hire_date, 'yyyy') in ('2005', 2006', '2007', '2008')

Upvotes: 2

Related Questions