CODEWITHSUNDEEP
mysqlsql
Harold Choi
Harold Choi

Reputation: 13

SQL sum up records with similar id

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!

Upvotes: 0

Views: 1516

Answers (2)

P.Salmon
P.Salmon

Reputation: 17655

I guess the tricky bit is getting 1 balance

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

Upvotes: 2

Saamo
Saamo

Reputation: 11

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.

Upvotes: 0

Related Questions