Beginner Oracle SQL Count Function Multiple columns

Question

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.

Something like this should work: SELECT COUNT(*) AS numEmpls, TO_CHAR(hire_date,'YYYY') AS theYear FROM employees GROUP BY TO_CHAR(hire_date,'YYYY') — David Starkey
– David Starkey, Commented Apr 1, 2013 at 19:31

Gordon Linoff · Accepted Answer · 2013-04-01 19:36:00Z

2

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')

answered Apr 1, 2013 at 19:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

knatz Over a year ago

Thank you for the help is there any advantage to adding the where clause

Jordan Over a year ago

To add to what Horus is stating, if the table has data from prior 2005 or after 2008 the count would be off in your first - 'Total' column.

Gordon Linoff Over a year ago

@Jordan . . . All that is true. But on re-reading the question, the total is larger than the total for the four years. Apparently, BlueFeet got it right. It seems strange to me to have a total in a row that is not the total for the four columns, but that is what the question is asking for.

Taryn · Accepted Answer · 2013-04-01 19:45:07Z

1

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

edited Apr 1, 2013 at 19:45

answered Apr 1, 2013 at 19:36

Taryn

249k57 gold badges374 silver badges409 bronze badges

1 Comment

Taryn Over a year ago

@knatz You are welcome, I added a version using PIVOT if you are using Oracle 11g+

Collectives™ on Stack Overflow

Beginner Oracle SQL Count Function Multiple columns

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related