AW: SQL Select - Average of top 25% records

Hi,

Why not simply using several Common Table Expressions:
1. X - Determines the Number of Records/Rows/Ranks to be selected (Ceiling
rounds up to the next full integer, i.e. 1.25 = 2 but 1 = 1)
2. Y - Determines the Rank of the B0Balance
3. z - Joins x and y to determine the Average of the 25% top balances
The final select statement joins y and z to get the average of the 25% into
each row.

With x as (Select Ceiling(Count(*) * .25) NbrOfRows
From CVACTDTL
Where B0Balance > 0),
y as (Select Rank() Over(Order By B0Balance Desc) MyRank, B0Balance
From CVACTDTL),
Z as (Select Avg(B0Balance) MyAvg
From y cross join x
Where MyRank <= NbrofRows)
Select y.*, MyAvg

From y cross join z;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Charles Wilt
Gesendet: Tuesday, 29. September 2009 22:29
An: Midrange Systems Technical Discussion
Betreff: Re: SQL Select - Average of top 25% records

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.