title: SQL Group By and Count on two columns
body: <p>I have a MySQL table with data as:-</p>
  
  <pre><code>country | city
  ---------------
  italy   | milan
  italy   | rome
  italy   | rome
  ireland | cork
  uk      | london
  ireland | cork
  </code></pre>
  
  <p>I want query this and group by the country and city and have counts of both the city and the country, like this:-</p>
  
  <pre><code>country | city   | city_count | country_count
  ---------------------------------------------
  ireland | cork   |          2 |             2
  italy   | milan  |          1 |             3
  italy   | rome   |          2 |             3
  uk      | london |          1 |             1
  </code></pre>
  
  <p>I can do:-</p>
  
  <pre><code>SELECT country, city, count(city) as city_count
  FROM jobs
  GROUP BY country, city
  </code></pre>
  
  <p>Which gives me:-</p>
  
  <pre><code>country | city   | city_count 
  -----------------------------
  ireland | cork   |          2 
  italy   | milan  |          1 
  italy   | rome   |          2
  uk      | london |          1
  </code></pre>
  
  <p>Any pointer to getting the country_count too?</p>