Re: Ntile Function

Put another layer of brackets around it then:

select

    id,
    sum(decode(percentile,10,amount)) Tenth,     sum(decode(percentile,20,amount)) Twentieth,     ....
    from (

        the previous select
    )
    group by id
;

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



JShrimip, Jr. wrote in message <3A43897B.27599B62_at_hotmail.com>...


>Yes that was it.  Oracle coverts Access97 from currency to varchar.

>Thank you very much and have a Happy Holiday from this side of the pond.

>

>I don't know if I'd be a good candiate for your book as I am strictly an

>PL/SQL end-user.

>

>

>P.S.   Is there a cross-tab query to transpose the final result to

>something like this:

>

>ID 10th 20th 30th 40th 50th 60th 70th 80th 90th 100th

>XXXXX   $48 $51 $53 $53 $57 $61 $64 $68 $75 $77

>

>

>

>Jonathan Lewis wrote:

>>

>> For the moment I'm baffled, I can't spot the error.

>>

>> >>     select id, percentile, max(amount)

>> >>     from (

>> >>         select id, amount, 10 * ntile(10) over (

>> >>                partition by id

>> >>                order by amount

          ) as percentile


>> >>         from normalised_table

>> >>         order by id, percentile      ****

>> >>     )

>> >>     group by id, percentile

>> >>     order by id, percentile

>> >>     ;

>>

>> The line marked **** is redundant by the way.

>>

>> The bit which goes

>> >>         ntile(10) over (

>> >>                partition by id

>> >>                order by amount

>> >>          ) as percentile

>>

>> means that Oracle should take all rows for an ID,

>> order them by amount, then add 'decile' values to

>> each row - so the order of the 'deciles' and the

>> order of the amount should match, which means

>> that you can't possibly get ---

>>

>> >27199203   50    $121.58

>> >27199203   60    $ 80

>>

>> Aha !! Flash of inspiration - is your amount

>> stored as a CHARACTER rather than a

>> number ???  Your output appears to have a

>> character-based ordered

>>

>> --

>> Jonathan Lewis

>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>> Practical Oracle 8i:  Building Efficient Databases

>>

>> Publishers:  Addison-Wesley

>> See a first review at:

>> http://www.ixora.com.au/resources/index.htm#practical_8i

>> More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html

>>

>> JShrimip, Jr. wrote in message <3A436BC5.2714EB09_at_hotmail.com>...

>> >This is very close BUT the percentiles are not ordered by amount.  When

>> >I try to order the result by amount,

>> >I get an error saying amount is not part of the group by function -

>> >(which it can't be).  Percentiles are arrayed from lowest to hightest.

>> >

>> >Here's the result from the code:

>> >

>> >ID    % Amount Should Be:

>> >

>> >27199203   10 $101 10 $ 80

>> >27199203   20 $109 20 $ 81

>> >27199203   30 $117 30 $ 81

>> >27199203   40 $117 40 $ 98

>> >27199203   50 $121.58 50 $ 98

>> >27199203   60 $ 80 60 $101

>> >27199203   70  $ 81 70 $109

>> >27199203   80 $ 81 80 $117

>> >27199203   90 $ 98 90 $117

>> >27199203  100 $ 98 100 $121.58

>> >

>> >How it is possible to order by Ascending Amount?