CODEWITHSUNDEEP
mysqlsql
Yoni Sherez
Yoni Sherez

Reputation: 13

select data from 2 tables

I have 2 tables:

tbl1 table

user_id amount
1       100
1       200

tbl2 table

user_id amount
1       900
1       800

I need to get the SUM of "amount" column from this tables for some user_id.

I can do it with 2 queries: 

SELECT SUM(amount) AS sum1 FROM tbl1 WHERE user_id = 1

SELECT SUM(amount) AS sum2 FROM tbl2 WHERE user_id = 1

How can I do it in 1 query?

The output should be: 

sum1 sum2
300  1700

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Here is one method:

SELECT (SELECT SUM(amount) AS sum1 FROM tbl1 WHERE user_id = 1) as amount1,
       (SELECT SUM(amount) AS sum2 FROM tbl2 WHERE user_id = 1) as amount2;

If you wanted to do this efficiently for multiple users, you could do:

SELECT (SELECT SUM(amount) FROM tbl1 t WHERE t.user_id = u.user_id) as amount1,
       (SELECT SUM(amount) FROM tbl2 t WHERE t.user_id = u.user_id) as amount2
FROM (SELECT 1 as user_id UNION ALL
      SELECT 2 as user_id
     ) u

This would be efficient if you had indexes on tbl1(user_id, amount) and tbl2(user_id, amount).

Upvotes: 1

Related Questions