How To Select the total sum of an entity that appears in two tables

Question

I Have two types of tractions tables where a user can be present in either one or both tables my Goal is to get a sum total for each user. the tables are as follows.

 users
------------+----------+
| user_id   | Name     |
+-----------+----------+
| 1         | John     |
------------+----------+
| 2         |  Wells   |
------------+----------+

 shop1
------------+----------+--------------+
| trans_id  | user_id  | amount_spent |
+-----------+----------+--------------+
| 1         |   1      |   20.00      |
------------+----------+--------------+
| 2         |   1      |   10.00      |
------------+----------+--------------+

 shop2
------------+----------+--------------+
| trans_id  | user_id  | amount_spent |
+-----------+----------+--------------+
| 1         |   2      |   20.05      |
------------+----------+--------------+

Expected Result after Summing

------------+-------------+
| user      | Total Spent |
+-----------+-------------+
| John      |   30.00     |
------------+-------------+
| Wells     |   20.05     | 
------------+-------------+

Answer 1

Use union all and group by:

select user_id, sum(amount_spent)
from ((select user_id, amount_spent from shop1) union all
      (select user_id, amount_spent from shop2)
     ) s
group by user_id;

That said, you have a poor data model. In general, tables with the same columns are a very bad idea. You should have a single table for all shops with an additional column for the shop.

If you want the name, you need to join:

select u.name, sum(amount_spent)
from users u join
     ((select user_id, amount_spent from shop1) union all
      (select user_id, amount_spent from shop2)
     ) s
     using (user_id)
group by user_id, u.name;