(Oracle) How to get SUM values based on values in other columns?

Question

Suppose there is data like below:

ID   Name  Cost  
ID1    A    10    
ID1    A    60    
ID1    B    20
ID1    C    20
ID2    B    10
ID2    B    50
ID2    C    50
ID3    B     5

Here in the table above, ID and NAME are not unique. And I want to get SUM values based on NAME, so the expected result is like below:

 ID   A_Costs  B_Costs  C_Costs  AB_Costs
 ID1    70       20       20       90
 ID2             60       50       60
 ID3             5                 5

A_Cost, B_Costs, and C_Costs are costs when the name is A, B or C. But what do I do if I want to get costs when the name is A and B? So what I was trying to do was this:

Select t2.ID,
SUM(DECODE (t2.name, 'A', t2.Cost, null)),
SUM(DECODE (t2.name, 'B', t2.Cost, null))
--(select sum(t1.cost) from table t1. where t1.name in ('A','B') and t1.id = t2.id)
from table t2
group by t2.id

But this does not work. How do I get the costs when the name is A and B like the line I commented out? Is there any effective way to get the value like that in one query?

Thank you in advance.

sstan · Accepted Answer · 2015-09-23 17:36:54Z

4

If you want to use decode(), you can do:

sum(decode(t2.name, 'A', t2.cost, 'B' t2.cost))

Or you can use a case statement:

sum(case when t2.name in ('A', 'B') then t2.cost end)

Full query:

select id,
       sum(case when name = 'A' then cost end) as a_costs,
       sum(case when name = 'B' then cost end) as b_costs,
       sum(case when name = 'C' then cost end) as c_costs,
       sum(case when name IN ('A', 'B') then cost end) as ab_costs
  from SomeTable
 group by id 
 order by id

SQL Fiddle Demo

answered Sep 23, 2015 at 17:36

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vamsi Prabhala · Accepted Answer · 2015-09-23 17:37:22Z

0

You will also have to aggregate after using sum in the inner query.

select 
id, max(a_cost) as A_Costs,  max(b_cost) as B_Costs,
max(c_cost) as C_Costs, nvl(max(a_cost),0) + nvl(max(b_cost),0) as AB_Costs
from (
select ID,
sum(case when name = 'A' then cost end) as a_cost,
sum(case when name = 'B' then cost end) as b_cost,
sum(case when name = 'C' then cost end) as c_cost
from table
group by id 
) t 
group by id

answered Sep 23, 2015 at 17:37

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Collectives™ on Stack Overflow

(Oracle) How to get SUM values based on values in other columns?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related