CODEWITHSUNDEEP
joingroup-bygoogle-bigquerysum
Chique_Code
Chique_Code

Reputation: 1530

Rolling sum of all values associated with the ids from two tables BigQuery

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

Upvotes: 0

Views: 240

Answers (1)

forpas
forpas

Reputation: 164139

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.

Upvotes: 1

Related Questions