CODEWITHSUNDEEP
sqlodbcdb2ibm-midrange
William
William

Reputation: 415

Top percentage of rows using SQL DB for i

I am trying to get a return that gives me the top 80% of the values returned. Using SQL DB for i the TOP clause will not work. I have seen some examples of using Count() in a nested select statement, but I am not sure how that is supposed to fit into the query I have already written. I already have 2 sub-query's so I need to find out how it would fit, or if it would work. Here is what I have so far:

Select  CATEGORY,
    LINE,
    ITEM#,
    Units
From    D*****.*****ST
Inner Join  (Select DW******.*****FO.ITEM,
                    Sum (SALES_UNITS) As Units,
                    CATEGORY
            From    DW*******.*****FO
            Inner Join  (Select CATEGORY,
                                DW****.******RY.ITEM
                         From   DW****.******RY
                         Where  CATEGORY='BRAKES') As CA***ST
            On DW*******.*****FO.ITEM=CA*****.***M              
            Where   ("DATE" between current date -1 years and current date) And (SALES > 5.00)
            Group By    DW*******.******O.ITEM,
                        CATEGORY) As Units_List
On      DW****.*****ST.**EM#=U*********.***M
Group By    CATEGORY,
            LINE,
            ITEM#,
            Units
Order By    Units DESC

So somewhere in here would be the nested Count() clause I'm assuming, I'm just not sure where it fits in the grand scheme of things. I'm still learning some of the intermediate SQL stuff, so I'm sorry if the question seems a little simple.

Upvotes: 2

Views: 847

Answers (3)

Marlin Pierce
Marlin Pierce

Reputation: 10079

Well, first of all, your sub-selects are unnecessary, and I find it easier to read with a simpler join statement. If you collapse your sub-queries, techniques with count(*) will be easier to incorporate.

Next, there is an issue with adding count to an existing query. If you are already using count, then adding a join and counting may mess up both counts, as the join creates a cartesian product, and count and sum may get the wrong answer. You don't have count or sum yet, so you don't have to worry about that pitfall.

Select  RY.CATEGORY, ST.LINE, ST.ITEM#, FO.Units
From    D*****.*****ST ST
  Inner Join DW*******.*****FO FO On ST.ITEM# = FO.ITEM
  Inner Join DW****.******RY RY On FO.ITEM = RY.ITEM
    And ("DATE" between current date -1 years and current date)
    And (SALES > 5.00)
  Inner Join D*****.*****ST ST_J On ST.LINE = ST_J.LINE And ST.ITEM# = ST_J.ITEM#
  Inner Join DW*******.*****FO FO On ST_J.ITEM# = FO_J.ITEM
Where FO_J.Units >= FO.Units
Group By    RY.CATEGORY, ST.LINE, ST.ITEM#, FO.Units
Having      Count(FO_J.Units) < 0.8 * (Select Count(*)
      From D*****.*****ST ST_J On ST.LINE = ST_J.LINE And ST.ITEM# = ST_J.ITEM#
      Inner Join DW*******.*****FO FO On ST_J.ITEM# = FO_J.ITEM)
Order By    FO.Units DESC

Upvotes: 1

Art
Art

Reputation: 5782

This is general Oracle example and maybe some help to you. I'm not sure which database you are using:

SELECT deptno, ename, sal
     , PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS prcnt
  FROM scott.emp
ORDER BY prcnt DESC, sal, ename
/

DEPTNO      ENAME   SAL     PRCNT
--------------------------------
10          MILLER  1300    1
...
20          ADAMS   1100    0.75
30          MARTIN  1250    0.6
...
10          CLARK   2450    0.5
...
30          TURNER  1500    0.4

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I would suggest using window functions. I find your query hard to follow, but here is the idea:

select t.*
from (select t.*,
             row_number() over (order by units desc) as seqnum,
             count(*) over () as totnum
      from (<view that gets you all the data you want>
           ) t
     ) t
 where seqnum <= 0.8*totnum

The idea is to use window functions to get the total count and also the ranking (I use row_number(), rank() might be more appropriate if you have ties). You can then just use a where clause to get the values you want.

Upvotes: 2

Related Questions