SQL sum up records with similar id-CodePudding

I have this table and I require to sum the payments and balance of the same id.

ID   BALANCE  PAYMENT  DATE
1    157      3        1/3/2021
1    157      4        3/3/2021
1    157      7        4/3/2021
1    157      8        9/3/2021
2    304      9        21/2/2021
3    208      3        18/5/2021

I need to get to this

ID   BALANCE  PAYMENT TOTAL
1    157      3       179 *(157 3 4 7 8)* 
1    157      4       179
1    157      7       179
1    157      8       179
2    304      9       313
3    208      3       211

And remove duplicates and eventually hide the payment column with a nested query to reduce confusion

ID   BALANCE  PAYMENT TOTAL
1    157      3       179
2    304      9       313
3    208      3       211

I tried using the 'select id, balance payment from table' however that does not take in the id, and group by does not seem to work as well.

Any help is much appreciated!

CodePudding user response：

I guess the tricky bit is getting 1 balance

SELECT ID, MAX(BALANCE) BALANCE,MAX(BALANCE)   SUM(PAYMENT) TOTAL
FROM T
GROUP BY ID

CodePudding user response：

Try this:

SELECT id,balance,payment,sum(payment) balance as TOTAL FROM TABLE_NAME group by id;

I tried a similar query here: https://www.programiz.com/sql/online-compiler/

SELECT customer_id,first_name,age,sum(age) customer_id as total FROM Customers group by first_name;

Maybe you'll need to specify all columns in group by but this is a concept that you can use for this scenario.