sql - Problems with group by and order by -

hi newbie world of sql struggling of basics work.

i have set of data looks this:

table name: sample     project      work order      amount      -----------------------------------------      111                         100      222             b             200      111             c             300      444             d             400      111             e             500      666             f             600 

i want end looking this:

table name: sample     project      work order      amount     project amount      --------------------------------------------------------      111             e             500           900      111             c             300           900      111                         100           900      666             f             600           600      444             d             400           600      222             b             200           200 

sorted project greatest total amount

group not work me groups projects one, can't see 3 work order lines "project 111"

project      work order      amount  -----------------------------------------  111                         900  222             b             200  444             d             400  666             f             600 

order not work can't sort out on basis of greatest project value

table name: sample     project      work order      amount      -----------------------------------------      666             f             600      111             e             500      444             d             400      111             c             300      222             b             200      111                         100 

my alternative idea if create column "project amount" calculates projects total based on values in "project" column , can sort project amount instead achieve desired format

table name: sample     project      work order      amount     project amount      --------------------------------------------------------      111             e             500           900      111             c             300           900      111                         100           900      666             f             600           600      444             d             400           600      222             b             200           200 

but struggling how column "project amount" calculate projects total value , present them on rows appear same project number.

any advise?

select  * ,       sum(amount) on (partition project) projamount ,       row_number() on     yourtable order         projamount desc 

example @ sql fiddle.

---

to select top 2 projects highest amounts, use dense_rank:

select  *    (         select  *         ,       dense_rank() on (order projamount desc) dr            (                 select  *                 ,       sum(amount) on (partition project) projamount                    yourtable                 ) withprojamount         ) withdenserank   dr < 3 order         projamount desc 

example @ sql fiddle.