CODEWITHSUNDEEP
sqloracle-database
jdgaub
jdgaub

Reputation: 191

Return the AVG of the Top 5 Values in Query Results

Question: How would I return the AVG of the Top 5 values in the Payment Ranking query below.

Database: Oracle 11g

Current Query

select distinct pye.payeeid, pay.value,
dense_rank() over (partition by bu.name order by pay.value DESC) as "Payment Ranking"

from cs_period per, cs_payee pye, cs_payment pay, cs_businessunit bu

where per.name = 'August 2014'
and pye.payeeseq = pay.payeeseq
and pay.businessunitmap = bu.mask
and pye.effectiveenddate = to_date('01/01/2200','dd/mm/yyyy')
and pye.removedate = to_date('01/01/2200','dd/mm/yyyy')
and per.periodseq = pay.periodseq
order by pay.value DESC

Results

enter image description here

Upvotes: 1

Views: 139

Answers (2)

David Faber
David Faber

Reputation: 12485

I'm assuming you want the top 5 according to "Payment Ranking" (for which you used DENSE_RANK() instead of RANK() or ROW_NUMBER()):

SELECT AVG(value) FROM (
    select distinct pye.payeeid, pay.value
         , dense_rank() over (partition by bu.name order by pay.value DESC) as payment_ranking
     from cs_period per, cs_payee pye, cs_payment pay, cs_businessunit bu
    where per.name = 'August 2014'
      and pye.payeeseq = pay.payeeseq
      and pay.businessunitmap = bu.mask
      and pye.effectiveenddate = to_date('01/01/2200','dd/mm/yyyy')
      and pye.removedate = to_date('01/01/2200','dd/mm/yyyy')
      and per.periodseq = pay.periodseq
) WHERE payment_ranking <= 5

Note that all I did was take your query and use it as a subquery, adding a WHERE clause to restrict to the top 5 ranked payments.

Upvotes: 1

OldProgrammer
OldProgrammer

Reputation: 12169

select avg(payment)
from
(
    <your query here>
)
where rownum <= 5

Assumed you keep the ordering as shown in your query.

Upvotes: 3

Related Questions