top 3 average sales [message #307239] |
Tue, 18 March 2008 04:12  |
sajut
Messages: 69 Registered: January 2007
|
Member |
|
|
Dear All
I am trying to get an output like this
itcode top_3_avg year_avg last_3_months_avg
----------------------------------------------------
it1 40 18.33 11.66
it2 55 15.75 0.0
from table a
itcode apr may june july august septe octo nov dec jan feb mar
it1 10 20 50 40 0 5 30 10 20 25 10 0
it2 50 75 40 10 5 0 7 2 0 0 0 0
Top 3 avg = (sum of top 3 months / 3)
year avg = sum of sales from apr to march / 12
last 3 months avg = (jan+feb+mar) / 3
Any help please
|
|
|
|
|
|
Re: top 3 average sales [message #307272 is a reply to message #307253] |
Tue, 18 March 2008 05:24   |
sajut
Messages: 69 Registered: January 2007
|
Member |
|
|
table x
it_code varchar2(10)
saleqty number
mth varchar2(12)
for each item there could be 12 or less rows in the table. I want to find out 3 months having the top sales, the average sales for these three months.
For this, I tried using,
select chd_item_code, sum(sum(saleqty)) over
(partition by chd_item_code) totsales
from polrep
group by chd_item_code
order by chd_item_code;
Here I am struck, how to get the top 3 months.
Regards
Saju
|
|
|
|
|
|