Re: SQL Select - Average of top 25% records

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.