Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003

    Question Unanswered: Query for Top X%


    (Using Oracle 9i)

    I have a resultset that has the structure (#ProductCode, Sales). I would like to know how to get the Products that are within the 80% of the sales total.

    Thanks a lot.

  2. #2
    Join Date
    Mar 2007
    I am just curious what tool is supposed to transform the source "resultset" to required result set. Note, that "Oracle" is quite broad term - database, Forms, Reports, apex, whatever else ...

    Anyway, in pure SQL, you would these three steps (as you did not post any code, I will not do so too):
    1) If productcode is not unique, aggregate by it firstly to get total sales for each one.
    2) Use SUM function in its analytic form to get cumulative sales sum (ordered by total sum desc) and total sales sum.
    3) Filter those rows which cumulative sum/total sum is less than 80% (shall the product exceeding 80% be present too?)
    Alternatively you may compute the least contributing products (ordered by total sum asc) and filter those products which cumulative sum/total sum is over 20%.

    For detailed description of analytic functions, please consult SQL Language Reference for your Oracle version. It is available with other Oracle documentation books e.g. online on
    Last edited by flyboy; 01-26-12 at 11:14. Reason: cumulative has only single 'm'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts