0

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
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
concatto
  • 1
  • 1
    read up on PIVOT or see this https://stackoverflow.com/questions/29020826/oracle-pivot-monthly-row-data-to-12-monthly-columns – OldProgrammer Apr 07 '22 at 18:23

1 Answers1

0

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
Littlefoot
  • 131,892
  • 15
  • 35
  • 57