make sum of one column and join it with another table-CodePudding

I have two tables and I want one query that gets sum of table 2 (addonAmount) column and combine it with table 1

table 1: subscriptions

 ------- -------- ------------- 
| subId | userId | subDuration |
 ------- -------- ------------- 
|    80 |   4607 | 6           |
|    81 |   4607 | 12          |
|    82 |   4608 | 18          |
 ------- -------- -------------

table 2: subscriptionAddons

 --------- ------- ------------- 
| addonId | subId | addonAmount |
 --------- ------- ------------- 
|      15 |    80 | 4           |
|      16 |    80 | 2           |
 --------- ------- -------------

Query I used:

SELECT subscriptions.*, subscriptionAddons.addonAmount
FROM subscriptions LEFT JOIN subscriptionAddons
ON subscriptions.subId = subscriptionAddons.subId;

what I want

 ------- -------- ------------- ------------ 
| subId | userId | subDuration |addonAmount |
 ------- -------- ------------- ------------ 
|    80 |   4607 | 6           |6           |
|    81 |   4607 | 12          |NULL        |
|    82 |   4608 | 18          |NULL        |
 ------- -------- ------------- ------------

CodePudding user response：

You need to group by the columns that define a record and sum the column you want as result.

SELECT s.subId, s.userId, s.subDuration, sum(sa.addonAmount) as addonAmount
FROM subscriptions s LEFT JOIN subscriptionAddons sa
    ON s.subId = sa.subId
GROUP BY s.subId, s.userId, s.subDuration;