Combine Google Sheets Query, IMPORTRANGE, and AVERAGE functions

Question

I'm trying to aggregate an average value using Query and IMPORTRANGE so I can compare the index numbers of the two tables.

=QUERY(IMPORTRANGE("Table ","Sheet!B:C"),"Select avg(Col2) where Col1='"&A2&"'",0)

File 1:

Table 1 Index	Above formula
1	=QUERY(IMPORTRANGE("URL","Sheet!I:J"),"Select avg(Col2) where Col1='"&A2&"'",0)

File 2:

Table 2 Index	Table 1 Index Match	Data
1	1	2.0
2	1	10.0

But it returns empty. When I remove avg(), then I get an error that the dataset is empty. So somewhere I made a mistake, and it's not comparing File 2 Column B against File 1 Column A properly. I have the IMPORTRANGE() selecting columns B and C, so that means I'm trying to average Col2 and comparing Col1 to A2, but I can't see the error there.

Answer 1

try:

=QUERY(IMPORTRANGE("ID", "Sheet!B:C"), 
 "select Col1,avg(Col2) 
  where Col1 is not null 
  group by Col1
  label avg(Col2)''", 0)