select data and sum of a column for a distinct column in data

Question

i have a table like this, (there is sum customers that each one has some payments):

customerID      Payments   InvoicCode
1                 1000         112
1                 250          456
2                 100          342
1                 20           232
2                 500          654
3                 300          230

what i want is like below (sum of a customer payments in each row):

customerID      Payments   InvoicCode   SumPayment
1                 1000         112        1270
1                 250          456        1270
2                 100          342        600
1                 20           232        1270
2                 500          654        600
3                 300          230        300

Answer 1

It's not proper normal form to have wider-scoped data duplicated in multiple rows. Think about the impact of updating a payment or adding a new customer payment will have - you will have to update all the relevant totals for that customer.

It would be simpler to create a view/stored procedure that gives you the totals at runtime which you can call whenever you need them:

create view CustomerTotals as

  select customerID
        ,sum(Payments) as SumPayment
    from mytable
group by customerID

Then you would reference this with select * from CustomerTotals with output like:

customerID      SumPayment
1               1270
2               600
3               300

Answer 2

Here it is:

SELECT t.customerID,
       t.Payments,
       t.InvoicCode,
       aux.SumPayment
FROM tablename t
INNER JOIN
(SELECT customerID,
        SUM(Payments) as SumPayment
 FROM tablename
 GROUP BY customerID) aux ON t.customerID = aux.customerID