Hello,
Following is the scenario:
Display the hiring count year wise for departments 20 and 30.
select extract(year from hire_date) year, count(employee_id) d20,null d30
from employees
where department_id = 20
group by extract(year from hire_date)
union
select extract(year from hire_date), null,count(employee_id)
from employees
where department_id = 30
group by extract(year from hire_date)
order by 1;
Result :
YEAR D20 D30
---------- ---------- ----------
2002 1
2003 1
2004 1
2005 1
2005 2
2006 1
2007 1
Intended Result:
YEAR D20 D30
---------- ---------- ----------
2002 1
2003 1
2004 1
2005 1 2
2005 2
2006 1
2007 1
Issue: Year is getting repeated depending on the values of count for the departments. Need help to get the count values of 1 and 2 for year 2005 in one row. (without using analytical functions .... partition by)
Thank you.