Andrei Ciobanu Andrei Ciobanu - 1 month ago 9
SQL Question

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 ?

A_M A_M
Answer

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.