SQL Grouping By

Using ORACLE SQL.

I have a table 'Employees' with one of the attributes 'hire_date' . My task (book exercise) is to write a SELECT that will show me how many employees were hired in 1995, 1996, 1997 and 1998 .

Something like:

TOTAL     1995     1996     1997     1998
-----------------------------------------
20        4        5        29       2

Individually is easy to count the number of employees for every year, eg:

SELECT
  COUNT(*),
FROM
  开发者_高级运维employees e
WHERE
  e.hire_date like '%95'

But I am having difficulties when I have to 'aggregate' the data in the needed format . Any suggestions ?

---

I'm assuming your hire_date is a varchar2, since you are doing a "like" clause in your example.

Will a simple table with one row per year do?

If so, try this in Oracle:

select case grouping(hire_date)
            when 0 then hire_date
            else 'TOTAL'
       end hire_date,
       count(hire_date) as count_hire_date
 from employees
 group by rollup(hire_date);

That should give something like:

hire_date  count_hire_date
1995       10
1996       20
1997       30
TOTAL      60

If you do need to pivot your results into something like you've shown in your question, then you can do the following if you know the distinct set of years prior to running the query. So for example, if you knew that you only had 1995, 1996 and 1997 in your table, then you could pivot the results using this:

SELECT
  MAX(CASE WHEN hire_date = 'TOTAL'    THEN ilv.count_hire_date   END) total,
  MAX(CASE WHEN hire_date = '1995'     THEN ilv.count_hire_date   END) count_1995,
  MAX(CASE WHEN hire_date = '1996'     THEN ilv.count_hire_date   END) count_1996,
  MAX(CASE WHEN hire_date = '1997'     THEN ilv.count_hire_date   END) count_1997
from (
    select case grouping(hire_date)
             when 0 then hire_date
             else 'TOTAL'
           end hire_date,
           count(hire_date) as count_hire_date
    from employees
    group by rollup(hire_date)
  ) ilv;

This has the obvious disadvantage of you needing to add a new clause into the main select statement for each possible year.

---

The syntax is not intuitive. This leverages cut'n'paste coding:

SQL> select
  2      sum(case when to_char(hiredate, 'YYYY') = '1980' then 1 else 0 end) as "1980"
  3      , sum(case when to_char(hiredate, 'YYYY') = '1981' then 1 else 0 end) as "1981"
  4      , sum(case when to_char(hiredate, 'YYYY') = '1982' then 1 else 0 end) as "1982"
  5      , sum(case when to_char(hiredate, 'YYYY') = '1983' then 1 else 0 end) as "1983"
  6      , sum(case when to_char(hiredate, 'YYYY') = '1987' then 1 else 0 end) as "1987"
  7      , count(*) as total
  8  from emp
  9  /

      1980       1981       1982       1983       1987      TOTAL
---------- ---------- ---------- ---------- ---------- ----------
         1         10          1          0          2         20

Elapsed: 00:00:00.00
SQL>

---

Here's how I'd do it in MySQL, don't know if this applies to Oracle too:

SELECT
 YEAR(hire_date), COUNT(*)
FROM 
 employees
GROUP BY 
 YEAR(hire_date)

---

SELECT NVL(hire_date,'Total'), count(hire_date) 
   FROM Employees GROUP BY rollup(hire_date);

If you need to PIVOT the data see A_M's answer. If you have years for which you have no data, yet still want the year to show up with a zero count you could do something like the following:

SELECT NVL(a.Year,b.Year), NVL2(a.Year,a.Count,0) FROM
(
SELECT NVL(hire_date,'Total') Year, count(hire_date) Count
   FROM Employees GROUP BY rollup(hire_date)
) a
FULL JOIN
(
   SELECT to_char(2000 + rownum,'FM0000') Year FROM dual CONNECT BY rownum<=9
) b ON a.Year = b.Year;

Here is some test data.

create table Employees (hire_date Varchar2(4));

insert into Employees values ('2005');
insert into Employees values ('2004');
insert into Employees values ('2006');
insert into Employees values ('2009');
insert into Employees values ('2009');
insert into Employees values ('2005');
insert into Employees values ('2004');
insert into Employees values ('2006');
insert into Employees values ('2006');
insert into Employees values ('2006');

---

Here's how I would do it in MS SQL - it will be similar in Oracle, but I don't want to try to give you Oracle code because I don't usually write it. This is just to get you a basic skeleton.

Select
  Year(e.hire_date),
  Count(1)
From
  employees e
Group By
  Year(e.hire_date)

---

I realize this is 6 years ago, but I also found another unique way of doing this using the DECODE function in Oracle.

select
count(decode(to_char(hire_date, 'yyyy') , '2005', hire_date, null)) hired_in_2005,
count(decode(to_char(hire_date, 'yyyy') , '2006', hire_date, null)) hired_in_2006,
count(decode(to_char(hire_date, 'yyyy') , '2007', hire_date, null)) hired_in_2007,
count(*) total_emp
from employees 
where to_char(hire_date,'yyyy') IN ('2005','2006','2007')