oracle查询问题

Question

有没有一种方法统计出每种类型中各种商品名称的总数量，相当于总统计：得到这样的表结构
下面附带有oracle表

商品类型    总数量      商品名称    数量
电脑          56         戴尔       16
                             IBM        30
                              HP         10

相机          12         自能       5
                             索尼       7

Answer 1

[code="sql"]
with temp as (
select 'computer' as goods_type ,'dell' as goods_name,6 as counts from dual
union
select 'computer' as goods_type ,'dell' as goods_name,5 as counts from dual
union
select 'computer' as goods_type ,'dell' as goods_name,5 as counts from dual
union
select 'computer' as goods_type ,'ibm' as goods_name,3 as counts from dual
union
select 'computer' as goods_type ,'ibm' as goods_name,10 as counts from dual
union
select 'computer' as goods_type ,'hp' as goods_name,10 as counts from dual
union
select 'camera' as goods_type ,'zineng' as goods_name,5 as counts from dual
union
select 'camera' as goods_type ,'zineng' as goods_name,10 as counts from dual
union
select 'camera' as goods_type ,'zineng' as goods_name,5 as counts from dual
union
select 'camera' as goods_type ,'sony' as goods_name,7 as counts from dual
)
select goods_type,goods_name,sum(counts) from temp group by goods_type,goods_name
[/code]