select Department 部门 ,
(sum(case pm when 'p' then 1 else 0 end)+sum(case pm when 'm'then 1 else 0 end)) as 计算机总数,
sum(case pm when 'p' then 1 else 0 end) as 台式机总数,
sum(case pm when 'm'then 1 else 0 end) as 笔记本总数
from COMPUTER group by Department
union all
select '合计',Sum(a.计算机总数),sum(a.台式机总数),sum(a.笔记本总数) from
(
select Department 部门 ,
(sum(case pm when 'p' then 1 else 0 end)+sum(case pm when 'm'then 1 else 0 end)) as 计算机总数,
sum(case pm when 'p' then 1 else 0 end) as 台式机总数,
sum(case pm when 'm'then 1 else 0 end) as 笔记本总数
from COMPUTER group by Department
) a
select Department 部门, (ComputerNo+PM) 计算机总数,ComputerNo 笔记本总数,PM 台式机总数
from COMPUTER union all
select '合计',Sum(ComputerNo+PM),sum(ComputerNo),sum(PM) from COMPUTER group by Department