Actually, I think you might have to do the second statement
dynamically instead of the static version I posted.

If I remember correctly, the FETCH FIRST clause can't use a host variable...


Charles

On Tue, Sep 29, 2009 at 4:26 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
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.




This thread ...

Follow-Ups:
Replies:

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

This mailing list archive is Copyright 1997-2019 by midrange.com 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].