初学者Oracle SQL计数功能多列

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.

创建一个查询,以显示员工总数,以及总数中2005,2006,2007和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.

任何帮助产生结果作为一个查询非常感谢。

2 个解决方案

#1

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

您正在尝试透视数据,因此您可以使用现有查询,但在聚合内添加CASE表达式:

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:

根据您的Oracle版本,您可以使用PIVOT功能:

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

请参阅两个查询的演示

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

#1