I have two tables in BigQuery. One table has date, id and name columns. For each name, there are a couple of ids associated with it. The data looks like this:
date id name
7/11 1 A
7/11 2 A
7/11 3 B
7/11 4 B
The other table has date, id, comments, shares columns in it. The id in this table comes without a name associated to it. The table looks like this:
date id comments shares
7/11 1 2 null
7/11 2 4 2
7/11 3 1 1
7/11 4 5 3
The end goal is to grab all the ids associated with a specific name (table 1) and sum up the comments and shares for the name or rather for the list of the ids (table 2) The desired output looks would look like this:
date name comments shares
7/11 A 6 2
7/11 B 6 4
You need a join of the 2 tables and aggregation:
SELECT t1.date, t1.name,
COALESCE(SUM(t2.comments), 0) comments,
COALESCE(SUM(t2.shares), 0) shares
FROM table1 t1 LEFT JOIN table2 t2
ON t2.date = t1.date AND t2.id = t1.id
GROUP BY t1.date, t1.name
See the demo.