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 Go to next message
weekend79
Messages: 188
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 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: select into query
Next Topic: Query using joins
Goto Forum:
  


Current Time: Fri Dec 09 17:08:27 CST 2016

Total time taken to generate the page: 0.25589 seconds