This discussion is archived
5 Replies Latest reply: Aug 27, 2013 1:57 AM by 205dde1d-1989-4f39-9044-7a0c1619ce92

# How to sum multiple columns with different grouping criteria

Currently Being Moderated

Currenly i am doing a project where i required to generate report which extracted from table. the table as per below

CURRENCY_A    AMOUNT_A    CURRENCY_B    AMOUNT_B    CURRENCY_C    AMOUNT_C

USD                      100                 EURO                  100              POUNDS             100

EURO                    200                 POUNDS             200              USD                    200

POUNDS               300                  USD                   300              EURO                  300

My expectations is the grand total sum of (AMOUNT_A+AMOUNT_B+AMOUNT_C) group by the currency

USD         :

EURO     :

POUNDS    :

Is it possible to perform this output in oracle alone?

• ###### 1. Re: How to sum multiple columns with different grouping criteria
Currently Being Moderated

Hi,

Have a look at Sum function (look at the aggregate version)

Regards,

Dariyoosh

• ###### 2. Re: How to sum multiple columns with different grouping criteria
Currently Being Moderated

Hi

Unpivot them before grouping / summing :

SELECT currency, sum(amount)

FROM (

SELECT CASE n WHEN 1 THEN currency_a

WHEN 2 THEN currency_b

WHEN 3 THEN currency_c end currency

,CASE n WHEN 1 THEN amount_a

WHEN 2 THEN amount_b

WHEN 3 THEN amount_c end amount

FROM your_table

CROSS JOIN (select column_value n from table(sys.odcinumberlist(1,2,3))) t

)

GROUP BY currency

• ###### 3. Re: How to sum multiple columns with different grouping criteria
Currently Being Moderated

or

```
select currency_a, sum(amount_a) from
(
select currency_a, amount_a from your_table union all
select currency_b, amount_b from your_table union all
select currency_c, amount_c from your_table
)
group by currency_a;```
• ###### 4. Re: How to sum multiple columns with different grouping criteria
Currently Being Moderated

Hi thanks, it really helps

• ###### 5. Re: How to sum multiple columns with different grouping criteria
Currently Being Moderated

thanks,

actually i tried this before but since my query originally is joining with a lot of table thus this solution is not really good since it will cause performance issue.

#### Legend

• Correct Answers - 10 points