CODEWITHSUNDEEP
mysqlsqljoin
Burning Hippo
Burning Hippo

Reputation: 805

Combine like primary keys in MySQL JOIN result

I have a query that is returning results like so:

Table Units  Table Payments
ID          |  Payment
1003        |  300.00
1003        |  100.00
1004        |  350.00
1005        |  200.00

How can I structure the query such that it combines the same IDs like so:

ID      |  Payment
1003    |  400.00
1004    |  350.00
1005    |  200.00

My query looks like:

SELECT u.id, p.amt FROM units u JOIN payments p ON u.id = p.unit

I have tried to SUM() the amt field but it only returns one result like so:

ID      |  Payment
1003    |  950.00

Upvotes: 0

Views: 57

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

SELECT u.id, sum(p.amt) as payment
FROM units u inner JOIN payments p ON u.id = p.unit
group by u.id

DEMO

Upvotes: 6

Related Questions