Say I have a table with columns: id, group_id, type, val
Some example data from the select:
1, 1, 'budget', 100
2, 1, 'budget adjustment', 10
3, 2, 'budget', 500
4, 2, 'budget adjustment', 30
I want the result to look like
1, 1, 'budget', 100
2, 1, 'budget adjustment', 10
5, 1, 'budget total', 110
3, 2, 'budget', 500
4, 2, 'budget adjustment', 30
6, 2, 'budget total', 530
Please advise,
Thanks.
This will get the you two added lines desired, but not the values for ID and type that you want.
Oracle examples: http://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm
Select id, group_id, type as myType, sum(val) as sumVal
FROM Table name
Group by Grouping sets ((id, group_id, type, val), (group_ID))
As @Serpiton suggested, it seems the functionality you're really looking for is the ability to add sub-totals to your result set, which indicates that rollup is what you need. The usage would be something like this:
SELECT id,
group_id,
coalesce(type, 'budget total') as type,
sum(val) as val
FROM your_table
GROUP BY ROLLUP (group_id), id, type
You can using union all to add more row to original select.
select group_id,type,val from tableA
union all
select group_id, 'budget total' as type,sum(val) as val from tableA group by group_id,type
To show right order and id you can using nested select
select rownum, group_id,type,val from (select group_id,type,val from tableA
union all
select group_id, 'budget total' as type,sum(val) as val from tableA group by group_id,type) order by group_id asc
with foo as
(select 1 group_id, 'budget' type, 100 val
from dual
union
select 1, 'budget adjustment', 10
from dual
union
select 2, 'budget', 500
from dual
union
select 2, 'budget adjustment', 30
from dual)
SELECT rank() over(order by type, group_id) rk,
group_id,
nvl(type, 'budget total') as type,
sum(val) as val
FROM foo
group by Grouping sets((group_id, type, val),(group_id))
its just the continuation of xQbert post to have id values!
