select product_line,sum(defect_qty),sum(inspect_qty)
from fr_badness
group by product_line
现在就是 sum(defect_qty),sum(inspect_qty)要过滤掉重复的条码，也就是重复的条码只算一次,相同条码的defect_qty和inspect_qty 一定是相同的，不同的分组一定没有相同的条码,如：
product_line barcode defect_qty inspect_qty position create_date
a 1101 2 10 t1 2011-10-11 21:05:09
a 1101 2 10 t2 2011-10-11 21:06:09
a 1102 9 20 t3 2011-10-11 21:07:09
b 1103 3 12 t4 2011-10-11 21:08:09
b 1103 3 12 t5 2011-10-11 21:07:09
a 1101 2 10 t1 2011-10-11 21:09:09
就是a的barcode为1101统计时只计算一次，b也是一样
统计结果应为：
product_line sum(defect_qty) sum(inspect_qty)
a 11 30
b 3 12
------解决方案--------------------
select t.product_line,sum(defect_qty),sum(inspect_qty)
from
(
select distinct
w.product_line ,
w.barcode ,
w.defect_qty ,
w.inspect_qty
from fr_badness w
) t
group by product_line;
------解决方案--------------------
------解决方案--------------------
- SQL code
select product_line,sum(defect_qty),sum(inspect_qty)from (select product_line,defect_qty,inspect_qty from fr_badness group by product_line,defect_qty,inspect_qty)group by product_line
------解决方案--------------------
- SQL code
------------------------------ Author :qiudf(day day up)-- Date :2011-10-12 9:10-- Verstion:------------------------------> 测试数据：[a]with a as(select 'a' as product_line , '1101' as barcode, '2' as defect_qty,'10' as inspect_qty, 't1' as position, sysdate as create_date from dualunion allselect 'a' as product_line , '1101' as barcode, '2' as defect_qty,'10' as inspect_qty, 't2' as position, sysdate as create_date from dualunion allselect 'a' as product_line , '1102' as barcode, '9' as defect_qty,'20' as inspect_qty, 't3' as position, sysdate as create_date from dualunion allselect 'b' as product_line , '1103' as barcode, '3' as defect_qty,'12' as inspect_qty, 't4' as position, sysdate as create_date from dualunion allselect 'b' as product_line , '1103' as barcode, '3' as defect_qty,'12' as inspect_qty, 't5' as position, sysdate as create_date from dualunion allselect 'a' as product_line , '1101' as barcode, '2' as defect_qty,'10' as inspect_qty, 't1' as position, sysdate as create_date from dual) select product_line,sum(defect_qty),sum(inspect_qty)from ( select product_line,barcode,defect_qty,inspect_qty,row_number() over(partition by product_line,barcode,defect_qty,inspect_qty order by product_line) rn from a )where rn=1 group by product_line--result:a 11 30b 3 12
------解决方案--------------------
#4楼的正解
------解决方案--------------------
select product_line,sum(distinct defect_qty),sum(distinct inspect_qty)
from 你的表
------解决方案--------------------