title: Mysql Subquery with Sum, Join and Group By
body: <p><strong>My Question</strong></p>
  
  <p>How to make the subquery sum the total of the mark with individual user? Currently i can only get the mark of first user, how can i get the mark for <strong>every user</strong>? 
  Here is the <em>fiddle</em> <a href="http://sqlfiddle.com/#!9/d6d68/3" rel="nofollow">http://sqlfiddle.com/#!9/d6d68/3</a></p>
  
  <p>The data set...</p>
  
  <pre><code>SELECT * FROM positions;
  +----+-----------+---------------+------------+---------------+---------------------+---------------------+
  | id | code_name | display_name  | category   | default_merit | created_at          | updated_at          |
  +----+-----------+---------------+------------+---------------+---------------------+---------------------+
  |  1 | K01       | Chairman      | College    |             8 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  |  2 | K02       | Vice Chairman | College    |             6 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  |  3 | K03       | Treasurer     | College    |             6 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  |  4 | K04       | Secretary     | College    |             6 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  |  5 | K05       | Committee     | College    |             4 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  |  6 | K06       | Participator  | College    |             1 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  |  7 | U01       | Chairman      | University |            10 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  |  8 | U02       | Vice Chairman | University |             8 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  |  9 | U03       | Treasurer     | University |             8 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  | 10 | U04       | Secretary     | University |             8 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  | 11 | U05       | Committee     | University |             5 | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |
  | 12 | U06       | Participator  | University |             2 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
  +----+-----------+---------------+------------+---------------+---------------------+---------------------+
  
  SELECT * FROM records;
  +----+---------+-------------+--------+---------+---------------+-----------------+------------+------------+------------+---------------------+---------------------+
  | id | user_id | position_id | finish | approve | recordable_id | recordable_type | deleted_at | created_by | approve_by | created_at          | updated_at          |
  +----+---------+-------------+--------+---------+---------------+-----------------+------------+------------+------------+---------------------+---------------------+
  |  1 |       1 |           1 |      1 |       1 |             1 | App\Event       | NULL       |          1 |          1 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
  |  2 |       1 |           2 |      1 |       0 |             2 | App\Event       | NULL       |          1 |          1 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
  |  3 |       2 |           2 |      1 |       0 |             2 | App\Event       | NULL       |          1 |          1 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
  |  4 |       2 |           2 |      1 |       0 |             2 | App\Club        | NULL       |          1 |          1 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
  |  5 |       2 |           2 |      1 |       0 |             1 | App\Sub_Event   | NULL       |          1 |          1 | 2016-06-08 10:30:55 | 2016-06-08 10:30:55 |
  |  6 |       3 |           2 |      1 |       0 |             2 | App\Club        | NULL       |          1 |          1 | 2016-06-08 10:30:56 | 2016-06-08 10:30:56 |
  |  7 |       2 |           3 |      1 |       0 |             2 | App\Club        | NULL       |          1 |          1 | 2016-06-08 10:30:56 | 2016-06-08 10:30:56 |
  +----+---------+-------------+--------+---------+---------------+-----------------+------------+------------+------------+---------------------+---------------------+
  
  SELECT * FROM users;
  +----+----------------+--------+-------------------------+----------+----------------+------------+---------------------+---------------------+-----------+----------+--------+-----------+-------------+-----------+------------+
  | id | name           | matrix | email                   | password | remember_token | deleted_at | created_at          | updated_at          | attention | resident | banned | meritbook | must_inlist | protected | last_login |
  +----+----------------+--------+-------------------------+----------+----------------+------------+---------------------+---------------------+-----------+----------+--------+-----------+-------------+-----------+------------+
  |  1 | Lim Yoong Kang | 47408  | yoongkang0122@gmail.com | Szha6... | NULL           | NULL       | 2016-06-08 10:30:53 | 2016-06-08 10:31:05 |         0 |        1 |      0 |         0 |           0 |         0 | NULL       |
  |  2 | Admin John     | 00001  | idiot.yk@gmail.com      | HYSPq... | NULL           | NULL       | 2016-06-08 10:30:53 | 2016-06-08 10:30:53 |         1 |        1 |      0 |         0 |           0 |         0 | NULL       |
  |  3 | Moderator Wong | 00002  | xuanyulao@gmail.com     | fvxWS... | NULL           | NULL       | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |         1 |        1 |      0 |         0 |           0 |         0 | NULL       |
  |  4 | Student Tai    | 00003  | taileepeng@gmail.com    | ihdF6... | NULL           | NULL       | 2016-06-08 10:30:54 | 2016-06-08 10:30:54 |         1 |        1 |      0 |         0 |           0 |         0 | NULL       |
  +----+----------------+--------+-------------------------+----------+----------------+------------+---------------------+---------------------+-----------+----------+--------+-----------+-------------+-----------+------------+
  </code></pre>
  
  <p>And the desired result</p>
  
  <pre><code>+--------+----------------+-------+
  | matrix | name           | total |
  +--------+----------------+-------+
  |      1 | Admin John     |     0 |
  |      2 | Moderator Wong |     0 |
  |      3 | Student Tai    |     0 |
  |      4 | Lim Yoong Kang |     8 |
  +--------+----------------+-------+
  </code></pre>
  
  <p><strong>The SQL query that I currently use</strong></p>
  
  <pre><code>SELECT
    `users`.`matrix`,
    `users`.`name`,
    (SELECT SUM(positions.default_merit)
     FROM `users`
       INNER JOIN `records` ON `records`.`user_id` = `users`.`id`
       INNER JOIN `positions` ON `positions`.`id` = `records`.`position_id`
     WHERE `records`.`finish` = '1' AND `records`.`approve` = '1' AND `users`.`deleted_at` IS NULL) AS total
  FROM `users`
  GROUP BY `users`.`matrix`
  HAVING total &gt;= 0
  
  LIMIT 100
  </code></pre>