title: MySQL ( v5.7.30 ) query AVG of AVGs
body: <p>So I have these 2 tables:</p>
  <p>jobs:</p>
  <pre><code>-------------------------------------------------
  id        business_id        other_columns
  -------------------------------------------------
  1         223                 xxxxxx
  -------------------------------------------------
  1         12                  xxxxxx
  -------------------------------------------------
  </code></pre>
  <p>businesses_ratings:</p>
  <pre><code>--------------------------------------------------------------------------------------
  id   business_id   professional   communication   safety   respectful   dependability        
  --------------------------------------------------------------------------------------
  1    223           4              2               5        4            3      
  --------------------------------------------------------------------------------------
  2    223           3              5               2        4            5
  --------------------------------------------------------------------------------------
  3    223           1              2               5        4            4
  -------------------------------------------------------------------------------------- 
  </code></pre>
  <p>I want to select the jobs of a particular business_id, and append to each job the overall rating of that business_id, computed as AVG((AVG(professional), AVG(communication), AVG(safety), AVG(respectful), AVG(dependability))</p>
  <p>Can I achieve this in one query?</p>
  <p>LE: I'll append here the query I've tried ( containing also the WHERE clause, maybe it'll help explaining better what I need to achieve. Also the thrown error:</p>
  <pre><code>SELECT * FROM jobs 
  CROSS JOIN (
      SELECT count(*) totalJobs FROM jobs 
      WHERE (
          ( JSON_CONTAINS(skills, '{&quot;id&quot;:1,&quot;val&quot;:&quot;Carpenter&quot;}') ) 
          AND  NOT JSON_CONTAINS(workers, '{&quot;id&quot;:6,&quot;fullname&quot;:&quot;Cip&quot;}') 
          AND NOT JSON_CONTAINS(applicants, '{&quot;id&quot;:6,&quot;fullname&quot;:&quot;Cip&quot;}')
      )
  ) ttl
  CROSS JOIN (
      SELECT AVG(
          (SELECT AVG(professional) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
          (SELECT AVG(communication) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
          (SELECT AVG(safety) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
          (SELECT AVG(respectful) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
          (SELECT AVG(dependability) FROM businesses_ratings WHERE business_id=jobs.business_id)
      ) business_rating FROM businesses_ratings WHERE business_id=jobs.business_id
  ) avg
  WHERE (
      ( JSON_CONTAINS(skills, '{&quot;id&quot;:1,&quot;val&quot;:&quot;Carpenter&quot;}') ) 
      AND  NOT JSON_CONTAINS(workers, '{&quot;id&quot;:6,&quot;fullname&quot;:&quot;Cip&quot;}') 
      AND NOT JSON_CONTAINS(applicants, '{&quot;id&quot;:6,&quot;fullname&quot;:&quot;Cip&quot;}')
  ) 
  ORDER BY start_date LIMIT 3
  </code></pre>
  <p>and the error:</p>
  <pre><code>Unknown column 'jobs.business_id' in 'where clause'
  </code></pre>