CODEWITHSUNDEEP
sqloracle11g
Abdurrahman Al-Amin Buluatie
Abdurrahman Al-Amin Buluatie

Reputation: 23

how to sum with case with oracle sql

I m having data in columns as:

item_id month_in amount
1 1 1500
1 1 1000
2 1 2500
3 1 2600
3 1 1000
4 1 2700
4 1 1000
1 2 1500
1 2 2000
2 2 1000
3 3 2500
3 3 2500
4 3 1000
4 3 2500

I want to have like this result

item_id januari februari maret
1 2500 3500 0
2 2500 1000 0
3 3600 0 0
4 3700 0 3500

in oracle sql query how to solve this. please help me

I have try this

select 
  item_id,
  (case  month_in=1  then sum(amout) end) AS januari
from table
group by item_id, month_in
order by item_id asc

but not working as I expected

Upvotes: 2

Views: 134

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

We can try a pivot query here:

SELECT
    item_id,
    SUM(CASE WHEN month_in = 1 THEN amount ELSE 0 END) AS januari,
    SUM(CASE WHEN month_in = 2 THEN amount ELSE 0 END) AS februari,
    SUM(CASE WHEN month_in = 3 THEN amount ELSE 0 END) AS maret
FROM yourTable
GROUP BY
    item_id
ORDER BY
   item_id;

Upvotes: 0

gaborsch
gaborsch

Reputation: 15758

You almost got it. This is the simplest solution, without extra hassle:

SELECT
    item_id,
    SUM(CASE WHEN month_in = 1 THEN amount ELSE 0 END) AS januari,
    SUM(CASE WHEN month_in = 2 THEN amount ELSE 0 END) AS februari,
    SUM(CASE WHEN month_in = 3 THEN amount ELSE 0 END) AS maret
from table
group by item_id
order by item_id asc

You don't need to define the months as a separate rowset, because the months are defined as values 1, 2, 3, ... as columns. Similarly, the items are defined by the group by function.

Upvotes: 0

Related Questions