Report Top X - Dev Shed

Dev Shed Forums Sponsor:

August 22nd, 2005, 09:26 AM

SBIN

Registered User

Join Date: May 2005

Posts: 5

Time spent in forums: 1 h 11 m 43 sec
Reputation Power: 0

Report Top X

Hi Guys,
I have a problem trying to report the top "X" sales and the %age sales of the top x for each Store
Due to technical reasons I do not have access to analytic functions on this server so I cannot use the Partition by or Rank.
I have enclosed something that "works" but unfortunately when I add further selects eg Product, date range and data from another table I get a big fat zero.
I know that rownum is assigned at the selection stage but I cannot figure out how to get any seelctions done.
Any hints gratefully accepted
Cheers

Code:

select store_no,total,big_total,round(total/big_total*100,2) pc
from
(select store_no , total from
   (select store_no, sum(qty_sold) total
      from sales_line
           group by store_No
           order by sum(qty_sold) desc)
           where rownum <= 20) ,
   (SELECT SUM(QTY_SOLD) big_total
         FROM sales_line where store_no in
		 (select store_no from
           (select store_no, sum(qty_sold)
               from sales_line
                group by store_No
               order by sum(qty_sold) desc)
            where rownum <= 20) )

STORE_NO        TOTAL  BIG_TOTAL         PC
---------- ---------- ---------- ----------
549765            381       1824      20.89
548141            121       1824       6.63
549825             98       1824       5.37
544321             93       1824        5.1
547622             90       1824       4.93
549351             89       1824       4.88
549810             84       1824       4.61
545585             79       1824       4.33
548985             78       1824       4.28
549833             76       1824       4.17
548802             74       1824       4.06
548117             70       1824       3.84
549745             70       1824       3.84
549551             66       1824       3.62
549420             64       1824       3.51
549472             61       1824       3.34
544909             60       1824       3.29
549955             59       1824       3.23
549533             56       1824       3.07
549869             55       1824       3.02

20 rows selected

August 25th, 2005, 06:15 AM

galith_haham

Contributing User

Join Date: Feb 2004

Posts: 64

Time spent in forums: 3 h 25 m 17 sec
Reputation Power: 5

Top

Hi,

you failed to mention which version of oracle you are in. in any case 9i has a new feture that might come in handy (don't know you will have to check it out) - use the first_rows_XX optimizer mode - the document sais "... now you can define for the optimizer that a spcific number of rows that you want returned by using the first_rows_n parameter". i never used it so i don't know if it will help.

Comments on this post

pabloj disagrees!

August 25th, 2005, 06:18 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,917

Time spent in forums: 2 Months 3 Weeks 3 Days 14 h 46 m 3 sec
Reputation Power: 279

The optimizer hint does not limit the number of rows returned like mysql's LIMIT clause.
Read more about it here

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

August 25th, 2005, 06:32 AM