'How to summarize by month and return result in column by month [duplicate]

I'm new to Oracle SQL and need help on writing a SQL script to calculate sales total by month, and return result set in total column by month. Thank you.

For example, input table:

DATE    ITEM    SALES
1/3/2021    A   1.00
1/5/2021    B   2.00
1/31/2021   A   3.00
2/5/2021    A   4.00
2/10/2021   B   5.00
2/25/2021   B   6.00
3/15/2021   B   7.00
3/20/2021   B   8.00
4/2/2021    B   9.00
4/5/2021    A   10.00
5/4/2021    A   11.00
5/10/2021   B   12.00
6/15/2021   A   13.00
6/19/2021   B   14.00
7/5/2021    B   15.00
7/10/2021   A   16.00
8/5/2021    A   17.00
8/10/2021   B   18.00
9/5/2021    B   19.00
9/15/2021   B   20.00
10/15/2021  A   21.00
10/17/2021  A   22.00
10/31/2021  A   23.00
11/12/2021  B   24.00
11/19/2021  B   25.00
12/15/2021  A   26.00

and result set with monthly total in each column:

ITEM    Jan   Feb   Mar     Apr     May     Jun     Jul     Aug  Sep  Oct   Nov   Dec

A       4.00  4.00  10.00   11.00   13.00   16.00   17.00        66.00            26.00
B       2.00  11.00 15.00   9.00    12.00   14.00   15.00   18.00     39.00 49.00
sqloraclepivot


Solution 1:[1]

One option is conditional aggregation:

select 
  item,
  sum(case when to_char(date_column, 'mm') = '01' then sales else 0 end as jan,
  sum(case when to_char(date_column, 'mm') = '02' then sales else 0 end as feb,
  ...
  sum(case when to_char(date_column, 'mm') = '12' then sales else 0 end as dec
from your_table
group by item

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Littlefoot

Related Questions