# Google Sheet join and aggregate

Given table A:

id value1 value2
1 0.33 0.25
2 0.4 0.7

and table B:

date id quantity
01/08 1 0.25
01/08 2 0.45
02/08 1 1
04/08 2 0.7

I would like to create a table which joins A and B by id and sums all value1 and value2 for all dates like this:

date value1 value2
01/08 0.25 * 0.33 + 0.4 * 0.45 0.25 * 0.25 + 0.45 * 0.7
02/08 0.33 0.25
03/08 0 0
04/08 0.7 * 0.4 0.7 * 0.7

How can I achieve this?

Use `query()`, like this:

``````=arrayformula(
query(
{
B!A1, A!B1:C1;
B!A2:A,
B!C2:C * iferror( vlookup(B!B2:B, A!A2:C, { 2, 3 }, false) )
},
"select Col1, sum(Col2), sum(Col3)
where Col1 is not null
group by Col1",
1
)
)
``````

This formula only shows the dates where data actually exists. To show all dates between the earliest and latest date and insert superfluous blank data in those rows, use this:

``````=arrayformula(
iferror(
vlookup(
sequence( max(B!A2:A) - min(B!A2:A) + 1, 1, min(B!A2:A) ),
query(
{
B!A2:A,
B!C2:C * iferror( vlookup(B!B2:B, A!A2:C, { 2, 3 }, false) )
},
"select Col1, sum(Col2), sum(Col3)
where Col1 is not null
group by Col1",
0
),
{ 1, 2, 3 },
false
),
iferror(
sequence( max(B!A2:A) - min(B!A2:A) + 1, 1, min(B!A2:A) ) / { 1, 0, 0 }
)
)
)
``````
• This is great. Thank you. Is it possible to insert missing dates? They don't appear when I try this code. Aug 14, 2021 at 10:03
• Edited the answer to add another formula. It is unclear why you would need to show those superfluous dates with no data. Is that an XY problem? Aug 14, 2021 at 14:05
• Well I suppose it's not a requirement. This aggregated table is visualized to me personally and I just prefer it this way. :) Thank you for your help. Aug 14, 2021 at 14:46