title: getting average values from a join
body: <p>I have this query in BigQuery. But I want the query to return the average engagement rate BY category. I'm very new to SQL, so not too sure how to do this. I tried adding in SELECT AVG(engagement_rate) but got error messsages.</p>
  <pre><code>SELECT engagement_rate,category_id, name
  FROM `public_instagram_channels` AS engrate
  JOIN `public_influencer_categories`AS categorydata
  ON engrate.engagement_rate=categorydata.influencer_id 
  JOIN `public_categories`AS categoryname
  ON categoryname.id=categorydata.category_id 
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">engagement_rate</th>
  <th style="text-align: center;">category_id</th>
  <th style="text-align: right;">name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1.5</td>
  <td style="text-align: center;">2.</td>
  <td style="text-align: right;">Beauty</td>
  </tr>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: center;">1.</td>
  <td style="text-align: right;">Home</td>
  </tr>
  <tr>
  <td style="text-align: left;">2.5</td>
  <td style="text-align: center;">3.</td>
  <td style="text-align: right;">Fashion</td>
  </tr>
  <tr>
  <td style="text-align: left;">3.2</td>
  <td style="text-align: center;">2.</td>
  <td style="text-align: right;">Beauty</td>
  </tr>
  <tr>
  <td style="text-align: left;">2.6</td>
  <td style="text-align: center;">2.</td>
  <td style="text-align: right;">Beauty</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>NEEDED OUTPUT:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">engagement_rate</th>
  <th style="text-align: center;">category_id</th>
  <th style="text-align: right;">name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">2.4</td>
  <td style="text-align: center;">2.</td>
  <td style="text-align: right;">Beauty</td>
  </tr>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: center;">1.</td>
  <td style="text-align: right;">Home</td>
  </tr>
  <tr>
  <td style="text-align: left;">2.5</td>
  <td style="text-align: center;">3.</td>
  <td style="text-align: right;">Fashion</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>THANK YOU IN ADVANCE</p>