Search code examples
sqloracle-databasegroup-byaggregationanalytic-functions

Grouping a column in oracle

I have three columns within my table: Amount, OrderNumber, Customerid

For each customerid there will be ordernumber and amount. Now i need to display customerid,Ordernumber and Amount(total Amount- for each customerid).

custid  srcnumber   amount
112     4344        20
112     7678        10
112     8766        30
34      6577        15
34      4566        5

Expected: 

custid  srcnumber   amount
112     4344        60
112     7678        60
112     8766        60
34      6577        20
34      4566        20
Solution

  • Use sum() over (partition by ..) analytic function to sum up the amount per each row :

     select Customerid as custid,
        OrderNumber as srcnumber,
        sum(amount) over ( partition by Customerid ) as amount
   from tab
  order by custid desc

    Demo