title: AVG() function is displaying average of all employees instead of average of employees in each town per shop
body: <p>I want to display the average number of employees in each town. Town can have </p>
  
  <p>This is my <code>data_base</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/ySKRc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ySKRc.png" alt="enter image description here"></a></p>
  
  <p>To clarify it's assumed that employers are part-timers so there may be a situation when they are currently not employed for the sake of exercising OUTER JOIN queries. They can also be assigned to more than 1 shop.</p>
  
  <p>I found the way to do it is to use subquery for AVG function. In a subquery, I will use COUNT to count the number of employees for each town and each shop and the main query will get AVG of those numbers grouping by the town. However while logically I feel the query should be working, it is displaying wrong data. Below is a query that I created.</p>
  
  <pre><code>SELECT s.Town, AVG(a.cnt) AS `Number of employees` FROM 
  (SELECT COUNT(k.EmpId) AS cnt FROM `Shops and Employees` k
  INNER JOIN Shops s ON s.ShopId = k.ShopId
  GROUP BY s.Town) AS a, shops s
  GROUP BY s.Town
  </code></pre>
  
  <p>I expected to see something like this:</p>
  
  <pre><code>Town name | AVG of employees per town
  -------------------------------------
  town1     | 3
  town2     | 5
  town3     | 1
  town4     | 4
  
  Instead I get this:
  
  Town name | AVG of employees per town
  -------------------------------------
  town1     | 2
  town2     | 2
  town3     | 2
  town4     | 2
  </code></pre>
  
  <p>Basically what I get is all employes summed divided by the number of towns. I want an average of employees for each town e.g. in town1 I have 2 shops. Shop1 has 2 employees while shop2 has 4 employees. An average for town1 should be 3.</p>