PHP code list all user with total value gather from other table

Question

table A

-----------------
a_id | user _a  | 
-----------------
1        |adam  |
2        |jose     |
3        |adam  |
4        |adam  |
5        |anne   |
6        |jose     |

table B

--------------------------------------
b_id | user_b   | value1 | value2 
--------------------------------------
1        |adam  | 33          | 9
2        |jose     | 46          |88
3        |adam  | 77          |21
4        |adam  | 81          |15
5        |anne   | 11          |67
6        |jose     | 45          |6

table C

--------------------------------------
c_id | user_c  | value1 | value2 
--------------------------------------
1        |adam  | 33          | 9
2        |jose     | 46          |88
3        |adam  | 77          |21
4        |adam  | 81          |15

table D

--------------------------------------
d_id | user_d   | value1 | value2 
--------------------------------------
1        |adam  | 33          | 9
2        |jose     | 46          |88

how can I view the total value of value1 and value2. example: I want my list view page look like this.

--------------------------------------------------------
user  | total value of value1 | total value of value2
---------------------------------------------------------
adam  |415                    | 99
jose    |183                     | 270
anne  |11                       | 67

pls. help.

Hint: Use `UNION ALL` to combine the three tables with the values, and use `SUM()` and `GROUP BY` to calculate the totals. — Barmar, Aug 28 '14 at 03:40
I have asked it before, here: http://stackoverflow.com/questions/12649119/select-total-of-childrens-in-multiple-tables-in-one-query-with-mysql — manix, Aug 28 '14 at 03:43

score 1 · Accepted Answer · answered Aug 28 '14 at 04:04

Here's one approach, to obtain the specified result using a SQL query (independent of any processing in PHP):

SELECT t.user_t      AS `user`
     , SUM(t.value1) AS `total value of value1`
     , SUM(t.value2) AS `total value of value2`
  FROM ( SELECT b.user_b      AS user_t
              , SUM(b.value1) AS value1
              , SUM(b.value2) AS value2
           FROM table_b b 
          GROUP BY b.user_b
          UNION ALL
         SELECT c.user_c      AS user_t
              , SUM(c.value1) AS value1
              , SUM(c.value2) AS value2
           FROM table_c c 
          GROUP BY c.user_c
          UNION ALL
         SELECT d.user_d      AS user_t
              , SUM(d.value1) AS value1
              , SUM(d.value2) AS value2
           FROM table_d d 
          GROUP BY d.user_d
       ) t
 GROUP BY t.user_t
 ORDER BY 2 DESC

PHP code list all user with total value gather from other table

1 Answers1