关于对某列进行分组求和的有关问题

关于对某列进行分组求和的问题
例如有一个table
格式为：
id pro_name cast
1 lass 100
1 tass 200
2 sass 100
3 mass 200
2 yass 100
1 uass 200
·····

现在希望能够按照id进行统计，得到的结果是：
1 lass 100
1 tass 200
1 uass 200
1 500 --这一行是统计id = 1的所有行cast的合计
2 sass 100
2 yass 100
2 200 --这一行是统计id = 2的所有行cast的合计
3 mass 200
3 200 --这一行是统计id = 3的所有行cast的合计

请问应该如何实现？

------解决方案--------------------
select id,pro_name,cast from (
select id,pro_name,cast from tbl1
union all
select id,'' as pro_name,sum(cast) from tbl1
group by id
)
order by id,pro_name

------解决方案--------------------

SQL code


select * from 
(
    select id,pro_name,cast from tbl1
    union all
    select id,'' "pro_name",sum(cast) "cast" from tbl1
    group by id
)
order by id,cast

------解决方案--------------------

SQL code


--创建测试表mytest
create table mytest(
id number,
pro_name varchar(20),
cast number
)
--插入数据
insert into mytest values(1,'lass',100)
insert into mytest values(1,'tass',200)
insert into mytest values(1,'sass',100)
insert into mytest values(1,'mass',200)
insert into mytest values(1,'yass',100)
insert into mytest values(1,'uass',200)

--分两步查询
1、以id、pro_name分组统计
select id,pro_name,sum(cast)sum_cast from mytest group by id,pro_name order by id
结果如下：

2、以id分组统计
select id,''pro_name,sum(cast)sum_cast from mytest group by id order by id
结果如下：
[img=http://b151.photo.store.qq.com/psb?/b1320946-81af-4671-9124-ef998a845dfb/p0Zmwitb3NTgDt62ic6X15nw4n6SUj.ArBY5DJB16hs!/b/YT59C1qMRQAAYvgGDVqGSgAA][/img]
--合并查询：
select a.* from(select id,pro_name,sum(cast)sum_cast from mytest group by id,pro_name)a
union all
select b.* from(select id,''pro_name,sum(cast)sum_cast from mytest group by id)b
--按id排序
select * from(
select a.* from(select id,pro_name,sum(cast)sum_cast from mytest group by id,pro_name)a
union all
select b.* from(select id,''pro_name,sum(cast)sum_cast from mytest group by id)b
) order by id,sum_cast

查询结果如下：
[img=http://b203.photo.store.qq.com/psb?/b1320946-81af-4671-9124-ef998a845dfb/D91LDiDJ9cDtM**O6XF3WPDwDUVHbB8kRPtniiq3gl8!/b/YX0hEHkesQAAYn0hEHkdsQAA][/img]

关于对某列进行分组求和的有关问题

相关推荐