CODEWITHSUNDEEP
sqloracle-databasegrouping-sets
HenlenLee
HenlenLee

Reputation: 445

How to rename the column with grouping sets in Oracle

I calculate the total amount using grouping sets

SELECT
CASE
    WHEN GROUPING(Name) = 1 THEN 'TOTAL' ELSE Name END,
ID,
SUM(Amount)   
FROM Table1 GROUP BY GROUPING SETS ( (ID, Name), (ID) );

I will get something like this 

ID      Name     Amount

11    company1    100
11    company1    200
11    TOTAL       300
22    company2    100
22    company2    200
22    TOTAL       300

But I want to change the name 'TOTAL' to 'comany1 -total' or 'company2-total' for example

ID         Name         Amount

11      company1          100
11      company1          200
11      company1-TOTAL    300
22      company2          100
22      company2          200
22      company2-TOTAL   300

Is it possible to do that? I am stuck on this for a while. Thanks!!!

Upvotes: 0

Views: 1250

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

How about phrasing this as:

SELECT (CASE WHEN GROUPING(Amount) = 1 THEN Name || '-TOTAL' ELSE Name END),
       ID, SUM(Amount)   
FROM Table1
GROUP BY GROUPING SETS ( (ID, Name, Amount), (ID, Name) );

This assumes that Amount is unique. If not, perhaps you have another column that is unique that you can use.

Upvotes: 0

Mladen S
Mladen S

Reputation: 93

this will give you what you want with grouping set:

with test_table as (
 select 11 ID, 'company1' Name, 100 Amount from dual union all
 select 11 ID, 'company1' Name, 200 Amount from dual union all
 select 22 ID, 'company2' Name, 100 Amount from dual union all
 select 22 ID, 'company2' Name, 200 Amount from dual
)

SELECT
  CASE
      WHEN GROUPING(AMOUNT) = 1 THEN NAME||'-TOTAL' ELSE Name END,
  ID,
  SUM(Amount)
FROM test_table
GROUP BY GROUPING SETS ( (ID, Name, AMOUNT), (ID, NAME));

and this is another way for this:

with test_table as (
 select 11 ID, 'company1' Name, 100 Amount from dual union all
 select 11 ID, 'company1' Name, 200 Amount from dual union all
 select 22 ID, 'company2' Name, 100 Amount from dual union all
 select 22 ID, 'company2' Name, 200 Amount from dual
)

select id, name, amount from (
  SELECT

    ID,
    name,
    Amount

  FROM test_table
  union all
  select id, name || '-TOTAL', sum(amount) from test_table  group by id, name || '-TOTAL'
) order by id, name, amount
;

Upvotes: 0

Related Questions