Home » SQL & PL/SQL » SQL & PL/SQL » How to get monthly average sum
How to get monthly average sum [message #685160] Mon, 01 November 2021 01:28 Go to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
Hi ,

I need to compute monthly average sum as at month aug, which I need to calculate the usage since day 1 of value to end of Aug and divide the qty since day 1 to end of aug as well. I am trying to use ADD_months but it give only the specific month usage value only, I need every month will be start from day 1 till the specific month for both qty & value to know the average value of an item. How should I do about it ?

My expected output should be

ITEM__Month________value
AAA___31/08/2020___0.50 I am getting the value from total value ( day 1 to aug 31 ) divide with total qty ( day 1 to aug 31), not only the Aug month total value & qty only. Same goes to all months.
AAA___30/09/2020___0.52 Total Value ( day 1 to Sep 30 ) divide with total qty ( day 1 to Sep 30 )
AAA___31/10/2020___0.52 Total Value ( day 1 to Oct 31 ) divide with total qty ( day 1 to Oct 31 )

Any suggestion or helps are welcome. Thanks.

Regards,
Lim

Re: How to get monthly average sum [message #685161 is a reply to message #685160] Mon, 01 November 2021 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: How to get monthly average sum [message #685162 is a reply to message #685161] Mon, 01 November 2021 02:37 Go to previous messageGo to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
Hi,

Sorry, my oracle version is 11g.

Raw data
ITEM   Month       RATE Qty Value
AAA    01/08/2020  0.50 10  5
AAA    05/08/2020  0.51 40  20.4
AAA    06/08/2020  0.50 50  25
Quote:
My expected results

ITEM   Month       value
AAA    31/08/2020  0.50
AAA    30/09/2020  0.52
AAA    31/10/2020  0.52
I am given raw data as in aug only as an example. It will total up Value/Qty to arrive end of month weighted average rate.
in this case total value is 50.4/100 = 0.504

Re: How to get monthly average sum [message #685163 is a reply to message #685162] Mon, 01 November 2021 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read again my last sentence and post accordingly. a test case must representative of your data.
Read again my last but one sentence and accordingly.

Re: How to get monthly average sum [message #685165 is a reply to message #685162] Mon, 01 November 2021 04:08 Go to previous messageGo to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
Hi,

Sorry sir, here again resubmit my problem. Hope this is fine now.

I need to compute monthly average sum as at month aug, which I need to calculate the usage since day 1 of value to end of Aug and divide the qty since day 1 to end of aug as well. I am trying to use ADD_months but it give only the specific month usage value only, I need every month will be start from day 1 till the specific month for both qty & value to know the average value of an item. How should I do about it ?


My Test case
create table t (item varchar(10), tdate date, qty number, rate number, value number)

insert into t ( item,tdate,qty,rate,value) values ( 'AAA', '05/08/2020', 40, 0.51,20.4);
insert into t ( item,tdate,qty,rate,value) values ( 'AAA', '01/08/2020', 10, 0.50,5);
insert into t ( item,tdate,qty,rate,value) values ( 'AAA', '06/08/2020', 50, 0.50,25);
insert into t ( item,tdate,qty,rate,value) values ( 'AAA', '15/09/2020'. 30, 0.53,15.9);
insert into t ( item,tdate,qty,rate,value) values ( 'AAA', '05/10/2020'. 40, 0.70,28);
My expected result
ITEM   Month       value
AAA    31/08/2020  0.50
AAA    30/09/2020  0.51
AAA    31/10/2020  0.52
As in month Aug only as an example. It will total up Value/Qty to arrive end of month weighted average rate.
in this case total value is 50.4/100 = 0.504


Thanks.
Re: How to get monthly average sum [message #685166 is a reply to message #685165] Mon, 01 November 2021 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No it is not:
  • '05/08/2020' as I said, this is NOT a date but a string, does it represent August, 5th or May, 8th? Always use TO_DATE with the appropriate format mask, or use the standard date literal.
  • SQL> insert into t ( item,tdate,qty,rate,value) values ( 'AAA', '05/10/2020'. 40, 0.70,28);
    insert into t ( item,tdate,qty,rate,value) values ( 'AAA', '05/10/2020'. 40, 0.70,28)
                                                                           *
    ERROR at line 1:
    ORA-00917: missing comma
Please, test your test case before posting it.

Re: How to get monthly average sum [message #685167 is a reply to message #685166] Mon, 01 November 2021 05:08 Go to previous messageGo to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
Sorry for copying the wrong code. Here the correct one.

create table t (item varchar(10), tdate date, qty number, rate number, value number)

insert into t ( item,tdate,qty,rate,value) values ( 'AAA', to_date('05/08/2020','dd/mm/yyyy'), 40, 0.51,20.4);
insert into t ( item,tdate,qty,rate,value) values ( 'AAA', to_date('01/08/2020', 'dd/mm/yyyy'), 10, 0.50,5);
insert into t ( item,tdate,qty,rate,value) values ( 'AAA', to_date('06/08/2020', 'dd/mm/yyyy') , 50, 0.50,25);
insert into t ( item,tdate,qty,rate,value) values ( 'AAA', to_date('15/09/2020', 'dd/mm/yyyy'), 30, 0.53,15.9);
insert into t ( item,tdate,qty,rate,value) values ( 'AAA', to_date('05/10/2020','dd/mm/yyyy'), 40, 0.70,28);

Re: How to get monthly average sum [message #685168 is a reply to message #685167] Mon, 01 November 2021 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This gives the result you specified:
Quote:
As in month Aug only as an example. It will total up Value/Qty to arrive end of month weighted average rate.
in this case total value is 50.4/100 = 0.504
but this is meaningless in real world:
SQL> select item, last_day(tdate),
  2         to_char(sum(value)/sum(qty),'990.000') value
  3  from t
  4  group by item, last_day(tdate)
  5  order by item, last_day(tdate)
  6  /
ITEM       LAST_DAY(TD VALUE
---------- ----------- --------
AAA        31-AUG-2020    0.504
AAA        30-SEP-2020    0.530
AAA        31-OCT-2020    0.700
Re: How to get monthly average sum [message #685169 is a reply to message #685168] Mon, 01 November 2021 07:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, since value = qty * rate all we need is:

select  item,
        last_day(tdate) month,
        to_char(avg(rate),'fm9999990.00') value
  from  t
  group by item,
           last_day(tdate)
  order by item,
           last_day(tdate)
/

ITEM       MONTH     VALUE
---------- --------- -----------
AAA        31-AUG-20 0.50
AAA        30-SEP-20 0.53
AAA        31-OCT-20 0.70

SQL>
SY.
Re: How to get monthly average sum [message #685176 is a reply to message #685169] Mon, 01 November 2021 21:07 Go to previous messageGo to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
Hi all,

Thanks for your reply. However, I am not interest on the average value for the month. I need to have the value for as at the month, meaning my value for Aug 2020 is total sum of value which is 20.4+5+25/40+10+50 = 0.50 ( because Aug is new month, hence it is only sum for Aug )

For sep, it need to sum value from Aug & Sep 20.4+5+25+15.9/40+10+50+30 = 66.3/130 = 0.51 ( when come to Sep it need to sum from Aug + Sep in value and divide with Qty in Aug + Sep )

For Oct, it need to sum value from Aug, sep & Oct 20.4+5+25+15.9+28 / 40+10++50+30+40 = 0.55 ( Aug+sep+oct (value) / Aug+sep+oct(qty )

My expected output will be :
ITEM   Month       value
AAA    31/08/2020  0.50
AAA    30/09/2020  0.51
AAA    31/10/2020  0.55
Re: How to get monthly average sum [message #685177 is a reply to message #685176] Tue, 02 November 2021 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SCOTT> with months as (select distinct last_day(tdate) mth from t)
  2  select item, mth "Month", to_char(sum(value)/sum(qty),'990.000') "value"
  3  from months left outer join t partition by (item) on tdate <= mth
  4  group by item, mth
  5  order by item, mth
  6  /
ITEM       Month          value
---------- ----------- --------
AAA        31-AUG-2020    0.504
AAA        30-SEP-2020    0.510
AAA        31-OCT-2020    0.555
Re: How to get monthly average sum [message #685178 is a reply to message #685177] Tue, 02 November 2021 01:25 Go to previous messageGo to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
Oh yes, it is working. Possible to help me quote in Oracle SQL as I am not really familiar with SQL. Thanks.
Re: How to get monthly average sum [message #685179 is a reply to message #685178] Tue, 02 November 2021 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another one:
SQL> with months as (select distinct item, last_day(tdate) mth from t)
  2  select m.item, m.mth "Month", to_char(sum(t.value)/sum(t.qty),'990.000') "value"
  3  from months m join t on t.item = m.item and t.tdate <= m.mth
  4  group by m.item, m.mth
  5  order by m.item, m.mth
  6  /
ITEM       Month          value
---------- ----------- --------
AAA        31-AUG-2020    0.504
AAA        30-SEP-2020    0.510
AAA        31-OCT-2020    0.555
Re: How to get monthly average sum [message #685180 is a reply to message #685179] Tue, 02 November 2021 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another one:
SCOTT> with
  2    data as (
  3      select item, last_day(tdate) tdate, sum(qty) qty, sum(value) value,
  4             min(last_day(tdate)) over (partition by item) first_month,
  5             max(last_day(tdate)) over (partition by item) last_month
  6      from t
  7      group by item, last_day(tdate)
  8    ),
  9    months as (
 10      select item, column_value mth
 11      from data,
 12           table(cast(multiset(select add_months(first_month, level-1)
 13                               from dual
 14                               connect by level <= months_between(last_month+1,first_month)+1)
 15                      as sys.odciDateList))
 16    )
 17  select m.item, m.mth "Month", to_char(sum(value)/sum(qty),'990.000') "value"
 18  from months m left outer join data d on d.item = m.item and d.tdate <= m.mth
 19  group by m.item, m.mth
 20  order by m.item, m.mth
 21  /
ITEM       Month          value
---------- ----------- --------
AAA        31-AUG-2020    0.504
AAA        30-SEP-2020    0.510
AAA        31-OCT-2020    0.555
Re: How to get monthly average sum [message #685181 is a reply to message #685179] Tue, 02 November 2021 03:59 Go to previous messageGo to next message
Lim
Messages: 20
Registered: January 2003
Junior Member
Thanks, sir. Great helps. I have learned a lot!
Re: How to get monthly average sum [message #685182 is a reply to message #685181] Tue, 02 November 2021 04:27 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What are the differences between the 3 queries?
They come when you consider several items with different months, possibly not consecutive.

Assume we add the following rows to your test case:
SQL> insert into t select 'BBB',tdate+rownum*60,2*qty,rate,value from t;

5 rows created.

SQL> select * from t order by 1, 2;
ITEM       TDATE              QTY       RATE      VALUE
---------- ----------- ---------- ---------- ----------
AAA        01-AUG-2020         10         .5          5
AAA        05-AUG-2020         40        .51       20.4
AAA        06-AUG-2020         50         .5         25
AAA        15-SEP-2020         30        .53       15.9
AAA        05-OCT-2020         40         .7         28
BBB        04-OCT-2020         80        .51       20.4
BBB        29-NOV-2020         20         .5          5
BBB        02-FEB-2021        100         .5         25
BBB        13-MAY-2021         60        .53       15.9
BBB        01-AUG-2021         80         .7         28

10 rows selected.

The first query gives the same months for all items even if some of them have no data for these months:
SQL> break on item skip 1 dup
SQL> with months as (select distinct last_day(tdate) mth from t)
  2  select item, mth "Month", to_char(sum(value)/sum(qty),'990.000') "value"
  3  from months left outer join t partition by (item) on tdate <= mth
  4  group by item, mth
  5  order by item, mth
  6  /
ITEM       Month          value
---------- ----------- --------
AAA        31-AUG-2020    0.504
AAA        30-SEP-2020    0.510
AAA        31-OCT-2020    0.555
AAA        30-NOV-2020    0.555
AAA        28-FEB-2021    0.555
AAA        31-MAY-2021    0.555
AAA        31-AUG-2021    0.555

BBB        31-AUG-2020
BBB        30-SEP-2020
BBB        31-OCT-2020    0.255
BBB        30-NOV-2020    0.254
BBB        28-FEB-2021    0.252
BBB        31-MAY-2021    0.255
BBB        31-AUG-2021    0.277

The second query gives, for each item, only the months where there are some data:
SQL> with months as (select distinct item, last_day(tdate) mth from t)
  2  select m.item, m.mth "Month", to_char(sum(t.value)/sum(t.qty),'990.000') "value"
  3  from months m join t on t.item = m.item and t.tdate <= m.mth
  4  group by m.item, m.mth
  5  order by m.item, m.mth
  6  /
ITEM       Month          value
---------- ----------- --------
AAA        31-AUG-2020    0.504
AAA        30-SEP-2020    0.510
AAA        31-OCT-2020    0.555

BBB        31-OCT-2020    0.255
BBB        30-NOV-2020    0.254
BBB        28-FEB-2021    0.252
BBB        31-MAY-2021    0.255
BBB        31-AUG-2021    0.277

The last one gives, for each item, all its months from the first one to the last one (and so add rows that don't exist in the table):
SQL> with
  2    data as (
  3      select item, last_day(tdate) tdate, sum(qty) qty, sum(value) value,
  4             min(last_day(tdate)) over (partition by item) first_month,
  5             max(last_day(tdate)) over (partition by item) last_month
  6      from t
  7      group by item, last_day(tdate)
  8    ),
  9    months as (
 10      select item, column_value mth
 11      from data,
 12           table(cast(multiset(select add_months(first_month, level-1)
 13                               from dual
 14                               connect by level <= months_between(last_month+1,first_month)+1)
 15                      as sys.odciDateList))
 16    )
 17  select m.item, m.mth "Month", to_char(sum(value)/sum(qty),'990.000') "value"
 18  from months m left outer join data d on d.item = m.item and d.tdate <= m.mth
 19  group by m.item, m.mth
 20  order by m.item, m.mth
 21  /
ITEM       Month          value
---------- ----------- --------
AAA        31-AUG-2020    0.504
AAA        30-SEP-2020    0.510
AAA        31-OCT-2020    0.555

BBB        31-OCT-2020    0.255
BBB        30-NOV-2020    0.254
BBB        31-DEC-2020    0.254
BBB        31-JAN-2021    0.254
BBB        28-FEB-2021    0.252
BBB        31-MAR-2021    0.252
BBB        30-APR-2021    0.252
BBB        31-MAY-2021    0.255
BBB        30-JUN-2021    0.255
BBB        31-JUL-2021    0.255
BBB        31-AUG-2021    0.277

The correct query (or maybe another one) depends on what your report should display.

[Updated on: Tue, 02 November 2021 05:05]

Report message to a moderator

Previous Topic: Oracle stored procedure with complex conditions
Next Topic: Days Query
Goto Forum:
  


Current Time: Fri Mar 29 02:58:46 CDT 2024