title: Group by and left join in BigQuery
body: <p>I have two tables as shown below:</p>
  
  <pre><code>Table 1
  
  col1|col2|col3
  A1  | 3  |  B1
  A1  | 4  |  C1
  A2  | 2  |  B1
  
  Table 2
  
  col1|col2|col3
  A1  | 4  |  2
  A1  | 5  |  3
  A1  | 11 |  5
  A2  | 0  |  10
  </code></pre>
  
  <p>I want to do a left join on <code>Table 1</code> and aggregate <code>col3</code> from <code>Table 2</code> as long as <code>col2</code> in <code>Table 2</code> is greater than <code>col2</code> from <code>Table 1</code> and less than equal to <code>col2</code>+7 from <code>Table 1</code>.</p>
  
  <p>The result would be as shown below</p>
  
  <pre><code>col1|col2|col3 |col4
  A1  | 3  |  B1 | 5
  A1  | 4  |  C1 | 8
  A2  | 2  |  B1 | null
  </code></pre>
  
  <p><strong>I have the following query but I get only about 50% of the records in Table 1 and don't see the nulls if applicable. There seems to be a problem with the where clause but I m not sure what is wrong.</strong></p>
  
  <pre><code>#standardSQL
  select A.col1, A.col2, A.col3, sum(B.col3) as col4
  from `Table 1` as A
  left join `Table 2` as B
  on A.col1 = B.col1
  where B.col2 &gt; A.col2 and B.col2 &lt;= A.col2+7
  group by 1,2,3
  </code></pre>