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  |
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 #685162 is a reply to message #685161] |
Mon, 01 November 2021 02:37  |
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
ITEM Month value
AAA 31/08/2020 0.50
AAA 30/09/2020 0.52
AAA 31/10/2020 0.52
in this case total value is 50.4/100 = 0.504
|
|
|
|
|
|
| Re: How to get monthly average sum [message #685165 is a reply to message #685162] |
Mon, 01 November 2021 04:08 |
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);
ITEM Month value
AAA 31/08/2020 0.50
AAA 30/09/2020 0.51
AAA 31/10/2020 0.52
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 |
 |
Michel Cadot
Messages: 68421
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 |
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 #685169 is a reply to message #685168] |
Mon, 01 November 2021 07:05 |
Solomon Yakobson
Messages: 3214
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>
|
|
|
|
| Re: How to get monthly average sum [message #685176 is a reply to message #685169] |
Mon, 01 November 2021 21:07 |
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 #685180 is a reply to message #685179] |
Tue, 02 November 2021 03:08 |
|
Michel Cadot
Messages: 68421
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 #685182 is a reply to message #685181] |
Tue, 02 November 2021 04:27 |
|
Michel Cadot
Messages: 68421
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 |
|
|
|
Goto Forum:
Current Time: Sat Apr 01 09:19:22 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
