You can't do it with one statement, you'll need two:

SELECT COUNT(*) * .25 FROM cvactdtl INTO :wTop25
WHERE b0balance > 0

with tbl as ( select b0balance FROM cvactdtl
WHERE b0balance > 0
order by b0balance desc
fetch first :wTop25 rows only
select avg(b0balance)
from tbl


On Tue, Sep 29, 2009 at 3:51 PM, Jack Prucha <jprucha@xxxxxxx> wrote:

I can get an average fo all the records.  The accounts without a balance
don't count.

SELECT AVG(b0balance) FROM cvactdtl
WHERE b0balance > 0

I can calculate 25% of the records

SELECT COUNT(*) * .25 FROM cvactdtl
WHERE b0balance > 0

But how can I calculate the average of the top 25% records.   Records with
the same balance would be counted separately.

I've looked at RANK and PARTITION BY but can't find enough information to
understand their use.

Any ideas?


This email, including any documents, files, or previous email messages attached to it, has been sent from an email account of College Foundation Inc., (CFI) and may contain confidential, proprietary, or legally privileged information belonging to CFI.  If you are not the intended recipient, any dissemination, distribution, or copying of this email or its attachments is strictly prohibited.  If you have received this email in error, please immediately notify the sender by email and destroy the original email and any attachments.

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].