title: SQL: Count more than average HAVING vs. WHERE
body: <p>I had a fairly straightforward interview question: return all countries that have more customers than the average number of customers of all cities.</p>
  
  <p><code>country</code> table:</p>
  
  <pre><code>id country_name
  1  Austria
  2  Germany
  3  United Kingdom
  </code></pre>
  
  <p><code>city</code> table:</p>
  
  <pre><code>id city_name country_id
  1  Wien      1
  2  Berlin    2
  3  Hamburg   2
  4  London    3
  </code></pre>
  
  <p><code>customer</code> table:</p>
  
  <pre><code>id customer_name city_id
  1  cust1         1
  2  cust2         4
  3  cust3         3
  4  cust4         1
  5  cust5         2
  6  cust6         1
  7  cust7         4
  8  cust8         2
  </code></pre>
  
  <p>Below is my solution. My answer was close but didn't pass the test case because mine returned more countries than the expected output. Do you know why, or how would you approach this problem?</p>
  
  <pre><code>SELECT 
      co.country_name, COUNT(customer_name)
  FROM
      country co
          JOIN
      city ci ON co.id = ci.country_id
          JOIN
      customer cu ON ci.id = cu.city_id
  GROUP BY co.country_name
  HAVING COUNT(customer_name) &gt; (SELECT 
          AVG(temp.cnt)
      FROM
          (SELECT 
              ci.id, COUNT(customer_name) AS cnt
          FROM
              city ci
          JOIN customer cu ON ci.id = cu.city_id
          GROUP BY ci.id) temp)
  ORDER BY co.country_name ASC;
  </code></pre>
  
  <p>Users in this thread <a href="https://stackoverflow.com/questions/31052969/sql-count-values-higher-than-average-for-a-group">SQL: Count values higher than average for a group</a> are using <code>WHERE</code> instead of <code>HAVING</code> to select value bigger than average. I wonder if <code>HAVING</code> is not suitable for this type of problem.</p>