Internal join with m / n relationship tables and the sum of a specific column

Question

I have three tables

• code
• of users
• codes_users

codescontains codes that must be assigned to a specific one users; codes_userscontains connection information.

SELECT users.*, 
       codes.code 
FROM   users 
       LEFT JOIN (codes 
                  INNER JOIN codes_users 
                          ON codes.id = codes_users.code_id) 
              ON users.id = codes_users.user_id

I managed to display the code with the user in this script: http://sqlfiddle.com/#!2/acd0cb/1

But when I like to count downloads, I get only one result:

SELECT users.*, 
       codes.code, 
       Sum(users.downloads) AS _downloadscount 
FROM   users 
       LEFT JOIN (codes 
                  INNER JOIN codes_users 
                          ON codes.id = codes_users.code_id) 
              ON users.id = codes_users.user_id 

http://sqlfiddle.com/#!2/acd0cb/2

and also I like counting the load of each user

Answer 1

At the end of your request, you need a group, telling the database that you want to count per user

SELECT users.*, 
       codes.code, 
       Sum(users.downloads) AS _downloadscount 
FROM   users 
       LEFT JOIN (codes 
                  INNER JOIN codes_users 
                          ON codes.id = codes_users.code_id) 
              ON users.id = codes_users.user_id 
GROUP  BY users.id; 

Answer 2

    SELECT users.*, codes.code, SUM(users.downloads) AS _downloadscount FROM users
LEFT JOIN
    (codes INNER JOIN codes_users ON codes.ID = codes_users.code_id)
     ON users.ID = codes_users.user_id
group by users.id