CODEWITHSUNDEEP
sqloracle-database
Ricky Kwan
Ricky Kwan

Reputation: 33

Oracle SQL: Adding row to select result

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.

Upvotes: 1

Views: 2659

Answers (4)

Allan
Allan

Reputation: 17429

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

Upvotes: 1

eliatou
eliatou

Reputation: 744

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!

Upvotes: 0

xQbert
xQbert

Reputation: 35323

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))

Upvotes: 2

Pham Thai Thinh
Pham Thai Thinh

Reputation: 98

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

Upvotes: 0

Related Questions