YoongKang Lim YoongKang Lim - 5 months ago 7
MySQL Question

Mysql Subquery with Sum, Join and Group By

My Question

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 every user?
Here is the fiddle http://sqlfiddle.com/#!9/d6d68/3

The data set...

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 |
+----+----------------+--------+-------------------------+----------+----------------+------------+---------------------+---------------------+-----------+----------+--------+-----------+-------------+-----------+------------+


And the desired result

+--------+----------------+-------+
| matrix | name | total |
+--------+----------------+-------+
| 1 | Admin John | 0 |
| 2 | Moderator Wong | 0 |
| 3 | Student Tai | 0 |
| 4 | Lim Yoong Kang | 8 |
+--------+----------------+-------+


The SQL query that I currently use

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 >= 0

LIMIT 100

Answer

What I think of is selecting a table with all default_merits in it, that match your criteria and then group it by user. This should lead to a table where all users only have the sum of their default_merits:

 SELECT
  `users`.`matrix`,
  `users`.`name`,
  IFNULL(`totals`.`total`, 0) as `total`
FROM `users`
  LEFT JOIN (
  SELECT
    `users`.`matrix`,
    SUM(positions.default_merit) as `total`
  FROM `users`
  LEFT JOIN `records` ON `records`.`user_id` = `users`.`id`
  LEFT JOIN `positions` ON `positions`.`id` = `records`.`position_id`
WHERE `records`.`finish` = 1 AND `records`.`approve` = 1 AND `users`.`deleted_at` IS NULL
GROUP BY `users`.`matrix`) AS `totals` ON `users`.`matrix` = `totals`.`matrix`