Home » SQL & PL/SQL » SQL & PL/SQL » Max(average sale) for any 3 parallel months (Oracle10g, Win2003)
Max(average sale) for any 3 parallel months [message #348570] |
Wed, 17 September 2008 05:19  |
weekend79
Messages: 198 Registered: April 2005 Location: Islamabad
|
Senior Member |

|
|
Hi
i have to find max (average sale) for any three parallel months for every item from table Monthly_Sales.
create table Monthly_Sales(Item_ID NUMBER(7) not null, MONTH_START_DATE DATE not null, Sale NUMBER not null);
insert into monthly_sales (item_id, month_start_date, sale) values (11923,to_date('10/1/1998','MM/DD/YYYY'),3);
insert into monthly_sales (item_id, month_start_date, sale) values (11923,to_date('11/1/1998','MM/DD/YYYY'),5);
insert into monthly_sales (item_id, month_start_date, sale) values (11923,to_date('12/1/1998','MM/DD/YYYY'),7);
insert into monthly_sales (item_id, month_start_date, sale) values (11923,to_date('1/1/1999','MM/DD/YYYY'),5);
insert into monthly_sales (item_id, month_start_date, sale) values (11923,to_date('2/1/1999','MM/DD/YYYY'),3);
insert into monthly_sales (item_id, month_start_date, sale) values (11923,to_date('3/1/1999','MM/DD/YYYY'),7);
insert into monthly_sales (item_id, month_start_date, sale) values (11967,to_date('7/1/1995','MM/DD/YYYY'),8);
insert into monthly_sales (item_id, month_start_date, sale) values (11967,to_date('8/1/1995','MM/DD/YYYY'),9);
select * from Monthly_Sales;
ITEM_ID MONTH_START_DATE SALE
11923 10/1/1998 3
11923 11/1/1998 5
11923 12/1/1998 7
11923 1/1/1999 5
11923 2/1/1999 3
11923 3/1/1999 7
11967 7/1/1995 8
11967 8/1/1995 9
I require a query that take calculate average sales as follows
for item_id=11923
for 1st 3 rows (3+5+7)/3=5
for 2nd 3 rows (5+7+5)/3=5.6
for 3rd 3 rows (7+5+3)/3=5
for 4th 3 rows (5+3+7)/3=5
so the result will be = 5.6
for item_id=11967
as there are less then 3 row
query will result as (8+9)/2=8.5
so the final result will be.
ITEM_ID Average_Sale
11923 5.6
11967 8.5
Thanks is advance
Wishes
J a w a d
[Updated on: Wed, 17 September 2008 05:19] Report message to a moderator
|
|
|
Re: Max(average sale) for any 3 parallel months [message #348575 is a reply to message #348570] |
Wed, 17 September 2008 05:31  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> with
2 data as (
3 select item_id,
4 avg(sale) over
5 (partition by item_id
6 order by month_start_date
7 rows between current row and 2 following
8 ) avg_sale,
9 count(*) over (partition by item_id) cnt,
10 count(*) over
11 (partition by item_id
12 order by month_start_date
13 rows between current row and 2 following
14 ) cur_cnt
15 from Monthly_Sales
16 )
17 select item_id, max(avg_sale) max_avg_sale
18 from data
19 where ( cur_cnt = 3 or ( cnt < 3 and cur_cnt = cnt ) )
20 group by item_id
21 order by item_id
22 /
ITEM_ID MAX_AVG_SALE
---------- ------------
11923 5.66666667
11967 8.5
2 rows selected.
Regards
Michel
[Updated on: Wed, 17 September 2008 05:32] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Thu Feb 13 17:26:00 CST 2025
|