Search code examples
sqloracle11g

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

Solution

  • 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;