Sum and substract rows from different tables-CodePudding

I have two tables

t1 with name, S, R.
t2 with name, Q.

I want a query to get S, R, Q Where R=S-sum of Qs with same name in t2. My code:

SELECT Q, name FROM T2 WHERE name IN (SELECT name FROM t1)

I can't select R

CodePudding user response：

Use a subquery to get the sums of Q for each name. Join that with t1 and do the subtraction.

SELECT t1.name, t1.R, t1.s - IFNULL(t2.sumQ, 0) AS R
FROM t1
LEFT JOIN (
    SELECT name, SUM(Q) AS sumQ
    FROM t2
    GROUP BY name
) AS t2 ON t1.name = t2.name