Home » SQL & PL/SQL » SQL & PL/SQL » top 3 average sales (oracle 9i, Linux)
top 3 average sales [message #307239] Tue, 18 March 2008 04:12 Go to next message
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 #307241 is a reply to message #307239] Tue, 18 March 2008 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is NOT an expert question. Why did you post this in expert forum and not in newbie one?

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide.

Regards
Michel
Re: top 3 average sales [message #307247 is a reply to message #307241] Tue, 18 March 2008 04:21 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
Sorry, from next time
Re: top 3 average sales [message #307253 is a reply to message #307247] Tue, 18 March 2008 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Now post waht you tried and where you are stuck.

Regards
Michel
Re: top 3 average sales [message #307272 is a reply to message #307253] Tue, 18 March 2008 05:24 Go to previous messageGo to next message
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
Re: top 3 average sales [message #307283 is a reply to message #307272] Tue, 18 March 2008 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The first table you gave and this one are completly different.
If you change the table, the query will change.

Quote:
I want to find out 3 months having the top sales

order by them by sal descending and take the 3 first ones.

Regards
Michel
Re: top 3 average sales [message #307307 is a reply to message #307283] Tue, 18 March 2008 07:29 Go to previous messageGo to next message
sajut
Messages: 69
Registered: January 2007
Member
There are different items with different months sales data in the table. I want for each item, the top 3 months average sales
Re: top 3 average sales [message #307309 is a reply to message #307307] Tue, 18 March 2008 07:41 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I saw you used an anlytical function, so I thought you understand it as order them partitioned by id.

Also post a test case, create table and insert statements.

Regards
Michel

[Updated on: Tue, 18 March 2008 08:02]

Report message to a moderator

Previous Topic: Use of Round() function.....Ceiling () function
Next Topic: join (merged 3)
Goto Forum:
  


Current Time: Sun Feb 09 22:06:18 CST 2025