Re: SQL Select - Average of top 25% records

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

Charles


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?

TIA
Jack



--
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.