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

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


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 mytablegroup by customerID

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

customerID      SumPayment1               12702               6003               300


Here it is:

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


Try this, (will mostly work on any rdbms)

SELECT  a.*, b. totalPaymentFROM    paymentsTable a            INNER JOIN            (                SELECT customerID, SUM(Payments) totalPayment                FROM paymentsTable                GROUP BY customerID            ) b ON a.customerID = b.customerID

SQLFiddle Demo