分组又一个问题

Question

表：employees                             
JOB_ID         SALARY DEPARTMENT_ID
---------- ---------- -------------
ad_pres      24000.00            90
ad_vp        17000.00            90
ad_vp        17000.00            90
it_prog       9000.00            60
it_prog       6000.00            60
it_prog       4200.00            60
st_man        5800.00            50
st_clerk      3500.00            50
st_clerk      3100.00            50
st_clerk      2600.00            50
st_clerk      2500.00            50
sa_man       10500.00            80
sa_rep       11000.00            80
sa_rep        8600.00            80
sa_rep        7000.00 
ad_asst       4400.00            10
mk_man       13000.00            20
mk_rep        6000.00            20
ac_mcr       12000.00           110
ac_account    8300.00           110所要的结果：
ac_account                                                   8300
ac_mcr                                                      12000
ad_asst                                                      4400
ad_pres                                          24000      24000
ad_vp                                            34000      34000
it_prog                                                     19200
mk_man          13000                                       13000
mk_rep           6000                                        6000
sa_man                                10500                 10500
sa_rep                                19600                 26600
st_clerk                   11700                            11700
st_man                      5800                             5800我写的sql语句：
select job_id,
decode(department_id,'20',sum(salary)) "dep 20",
decode(department_id,'50',sum(salary)) "dep 50",
decode(department_id,'80',sum(salary) )"dep 80",
decode(department_id,'90',sum(salary)) "dep 90",
sum(salary) total
from employees
group by job_id,department_id
order by job_id结果为：
 JOB_ID         dep 20     dep 50     dep 80     dep 90      TOTAL
---------- ---------- ---------- ---------- ---------- ----------
ac_account                                                   8300
ac_mcr                                                      12000
ad_asst                                                      4400
ad_pres                                          24000      24000
ad_vp                                            34000      34000
it_prog                                                     19200
mk_man          13000                                       13000
mk_rep           6000                                        6000
sa_man                                10500                 10500
sa_rep                                19600                 19600
sa_rep                                                       7000
st_clerk                   11700                            11700
st_man                      5800                             5800请问大家：如何把sa_rep 合并，salary=7000那个值，没有department_id？

Answer 1

你的两个sa_rep应该有所不同，比如多了空格之类的

Answer 2

没看仔细，主要原因是你的group by job_id,department_id 
把这句改成group by job_id

Answer 3

其中一个sa_rep ，它的department_id为空，所以才有SA_REP，现在我的问题是，怎么样能把department_id为空的sa_rep合并，得到我想要的结果

Answer 4

select job_id,
sum(decode(department_id,'20',salary,0)) "dep 20",   --如果是20，加进去，否则0
sum(decode(department_id,'50',salary,0)) "dep 50", --同上
sum(decode(department_id,'80',salary,0)) "dep 80", --同上
sum(decode(department_id,'90',salary,0)) "dep 90", --同上
sum(salary) total
from employees
group by job_id
order by job_id 
试试看？

Answer 5

在这个查询结果的基础上继续GROUP BY job_id就好了啊