SQL group by (문제)

--부서별 연도별 01~03 04~06 / 07~나머지년도 인원을 계산하세요.
--      -연도(column)
--부서(row)
select department_id, count(*),
    count(decode(to_char(hire_date,'yyyy'), 2001, 1, 2002, 1, 2003, 1)) as "1~3",
    count(decode(to_char(hire_date,'yyyy'), 2004, 1, 2005, 1, 2006, 1)) as "4~6",
    count(decode(to_char(hire_date,'yyyy'), 2007, 1, 2008, 1, 2009, 1)) as "7~"
from employees  group by department_id  order by department_id asc;
 
--부서20,50,80,90에 대해 직무, 부서에 해당하는 급여 및 해당직무에 대한 총 급여를 표시하도록한다.
--job dept20 dept50... total
--ac_mgr nul nul nul ...
--st_man null 58000...
--job_id별로, 
 
select job_id,
sum(decode(department_id, 20, salary)) as dept20,
sum(decode(department_id, 50, salary)) as dept50,
sum(decode(department_id, 80, salary)) as dept80,
sum(decode(department_id, 90, salary)) as dept90,
 
--sum(decode(department_id, 20,salary,50,salary,80,salary,90,salary)) as total
sum(salary) as total
from employees  
where department_id in(20,50,80,90)
group by job_id;
 
select job_id,
sum(decode(department_id, 20, salary)) as dept20,
sum(decode(department_id, 50, salary)) as dept50,
sum(decode(department_id, 80, salary)) as dept80,
sum(decode(department_id, 90, salary)) as dept90,
sum(decode(department_id, 20,salary,50,salary,80,salary,90,salary)) as total
from employees  
group by job_id;