R H R H - 1 year ago 60
SQL Question

OUTER JOIN but also adding up the amount

Right this is a bit of a confusing which I'm not sure is possible through sql.

My first table is called members, I currently have this


This is the data on the table
members table

I also have another table with the following information.


What I want to do is run a query, which will retrieve all the first names, lastnames userID and the amount.

But only retrieve the amount if the userID is in both tables. I've got this currently working here. I'm getting the following results back:

Donations table (top image) results(bottom)

What I'm trying to is add all the amount with the associated userID and bring it back as one. So this is what I'm trying to achieve.

Rick Rees 1 82

Laura Sammy 127 0

Could this be achieved with sql or would I have to use php ?

this is my SQL query at the moment

SELECT members.firstname, members.lastname, members.userID, donations.amount
FROM members
INNER JOIN donations ON members.userID = donations.userID
ORDER BY members.userID ASC

Hopefully this makes sense, sorry if its not detailed enough this is my first post. Appreacitae any help please ?

Answer Source

In mysql you can group the results by a column (or multiple columns). When you have used the GROUP BY command it allows you to use some aggregate functions like AVG (Average of all rows in the group) SUM, MAX MIN COUNT etc. It's very handy for finding the number of posts linked to a user etc. Or the maximum purchase of all a users purchases

 SELECT members.firstname
     , members.lastname
     , members.userID
     , **SUM(donations.amount) AS amount**
  FROM members
INNER JOIN donations 
    ON members.userID = donations.userID
 **GROUP BY members.userID** 
 ORDER BY members.userID ASC 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download