sum的高級使用

Question

SQL語句如下：
select 
t.s_date
,t.dept
,t.model
,0     "QC_QTY"
,SUM(nvl(t.QC_QTY,0)-nvl(T.TURN_NEXT,0)+nvl(t.PRE_COME,0)+nvl(t.NEXT_BACK,0)+nvl(t.REPAIR,0)-nvl(t.PICKING,0)-nvl(t.SCRAP,0)-nvl(t.BACK_PRE,0))        "sum_qty"
from
 wip.xwp_xwpa136l_qc t
where  
 t.s_date  between '20110701' and '20110701'
GROUP BY
t.s_date
,t.dept
,t.model
 
unionselect 
t.s_date
,t.dept
,t.model
,SUM(nvl(t.QC_QTY,0))     "QC_QTY"
,0       "sum_qty"
from
 wip.xwp_xwpa136l_qc t
where  
 t.s_date  between '20110701' and '20110701'
GROUP BY
t.s_date
,t.dept
,t.model 
結果如下：
S_DATE         DEPT MODEL QC_QTY sum_qty
20110701 EP1110 10320 0 591
20110701 EP1110 10320 320 0
20110701 EP1110 40059 0 53
20110701 EP1110 40059 60 0
20110701 EP1110 40059LH 0 41
20110701 EP1110 40059LH 28 0
20110701 EP1110 40568 0 145
20110701 EP1110 40568 33 0
如何改寫我的sql語句，或用其它方法
好讓上面的結果合並成下面的,就是把型號MODEL相同的QC_QTY、sum_qty合並
S_DATE         DEPT MODEL QC_QTY sum_qty
20110701 EP1110 10320 320 591
20110701 EP1110 40059 60 53
20110701 EP1110 40059LH 28 41
20110701 EP1110 40568 33 145 

Answer 1

--外层再嵌套一个group by
select s_date,dept,model,sum(qc_qty) as qc_qty,sum(sum_qty) as sum_qty
from 
(
select  
t.s_date
,t.dept
,t.model
,0 "QC_QTY"
,SUM(nvl(t.QC_QTY,0)-nvl(T.TURN_NEXT,0)+nvl(t.PRE_COME,0)+nvl(t.NEXT_BACK,0)+nvl(t.REPAIR,0)-nvl(t.PICKING,0)-nvl(t.SCRAP,0)-nvl(t.BACK_PRE,0)) "sum_qty"
from
 wip.xwp_xwpa136l_qc t
where   
 t.s_date between '20110701' and '20110701'
GROUP BY
t.s_date
,t.dept
,t.model
 
unionselect  
t.s_date
,t.dept
,t.model
,SUM(nvl(t.QC_QTY,0)) "QC_QTY"
,0 "sum_qty"
from
 wip.xwp_xwpa136l_qc t
where   
 t.s_date between '20110701' and '20110701'
GROUP BY
t.s_date
,t.dept
,t.model
) 
group by s_date,dept,model

Answer 2

外面再执行一个查询
select decode(a,0,b,a) from (你的查询)