title: Oracle SQL PERCENT_RANK Skewed When Dupe Values?
body: <p>Good afternoon:  I'm trying to determine the best way to get a percent of products sold that make up 80% of sales cases (and/or dollars).  Last year, it was reported at 20%. I'm getting the same thing this year, but I'm apprehensive to believe that being that it appears to be a direct correlation between the percent of sales I need to the percent left over.  For example, if I want 90% of sales, then I get 10% of products.</p>
  
  <p>Although the formulas seem to work in the query below, I'm wondering if it's skewed because of dupe values in the sales cases.  If I were to add up 80% of the sales cases, then the count of products is much lower. But I don't know if the query is skewed because of those dupes, and it gives me 80% at a higher value. I also saw that there's an aggregate percent_rank - should I be trying that one? </p>
  
  <p>Here's an example of the query where I'm trying a couple of option to get to a more reasonable value. Thanks in advance!</p>
  
  <pre><code>SELECT
      sales_cases,
      total_sales_cases,
      total_item_count  ,
      NVL(RowNbrCs,0) as RowNbrCs,
  
      PERCENT_RANK() OVER(PARTITION BY company_id  ORDER BY  (sales_cases / total_sales_cases) ASC )  as Rank_by_Cases ,
      PERCENT_RANK() OVER(PARTITION BY company_id  ORDER BY   (NVL(RowNbrCs,0) / sum(total_sales_cases)) ASC )  as Rank_by_Cases 
  
  FROM (
      SELECT
          company_id  ,
          prod_id ,
          SUM( NVL(sales_cases,0)) ,0)  as sales_cases,
          SUM( NVL(net_sales,0)) ,0)  as net_sales,
          ROUND( SUM(( SUM( NVL(sales_cases,0)) )) OVER(PARTITION BY company_id ),0)  as total_sales_cases,
          ROUND( SUM(( SUM( NVL(net_sales,0)) )) OVER(PARTITION BY company_id ),0)  as total_net_sales,
          ROW_NUMBER() OVER(PARTITION BY company_id  ORDER BY  SUM( NVL(sales_cases,0)) ASC ) as   RowNbrCs,
          ROW_NUMBER() OVER(PARTITION BY case when company_id  ORDER BY  SUM( NVL(net_sales,0)) ASC ) as   RowNbrSls,
          SUM(( COUNT(distinct prod_id) )) OVER() as total_item_count 
  
      from sales, customer, product
      where &lt;&lt; conditions &gt;&gt;
      group by
          company_id  ,   prod_id
  )
  group by    
      sales_cases ,
      total_sales_cases ,
      total_item_count ,
      RowNbrCs ,
      company_id
  order by  RowNbrCs desc
  </code></pre>