Combine data from two tables into one output SQL

Question

I have two tables, as below:

I'm hoping to create a view, where the result is as per the result above. That is, the Act column is the total of all matching records between tbl1 and tbl2.

Additional explaination, graphically:

You're looking for JOIN and aggregate functions (specifically SUM). What have you tried so far? Please do share your attempts so we can show you where you might have gone wrong, or not quite got it right. — Thom A
– Thom A ♦, Commented Feb 16, 2019 at 19:04

Mureinik · Accepted Answer · 2019-02-16 19:07:01Z

2

I'd left join tbl1 with an aggregate query on tbl2:

SELECT    t1.id, t1.req, COALESCE(t2.act, 0) AS act
FROM      tbl1 AS t1
LEFT JOIN (SELECT   id, SUM(act)
           FROM     tbl2
           GROUP BY id) t2 ON t1.id = t2.id

answered Feb 16, 2019 at 19:07

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combine data from two tables into one output SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related