SQL how do I sum up a has many relationship

So I have the following table, I managed to join users and membership tables just fine with a left join however I’ve been unsuccessful at summing up the individual customers’ total.

Here’s my code, the one-to-one associations seem to be doing fine however the summing up of the total seems to not display, what am I doing wrong? is there a different way of summing up a one-to-many association?

SELECT name, membership.userId as customerId, SUM(sales.total) as Total 
FROM users

LEFT OUTER JOIN membership ON membership.userId = users.id
LEFT OUTER JOIN sales ON buyerId = users.id

Tables

Users table:
id     name     type 
1      John     Customer
2      Adam     Customer
3      Robert   Customer

Membership table:
id     userId 
1      1
2      2
3      3


Sales table:
buyerId  total
1        12
1        20
1        5
2        5
2        10
3        5
3        5

Desired output:

Sales Report:
Name      CustomerId   Total
John      1            37
Adam      2            15
Robert    3            10

Answer

SELECT name, membership.userId as customerId, SUM(sales.total) as Total 
FROM users

LEFT OUTER JOIN membership ON membership.userId = users.id
LEFT OUTER JOIN sales ON buyerId = users.id
GROUP BY name, customerId

You need to group by user.

Advertisement

Answer