title: MySQL - Calculate number of males and females grouped by country
body: <p>I have a table with 3 columns being <code>country</code>, <code>user_id</code>, <code>gender</code>.
  The <code>user_id</code> just increments, there are a number of different countries and the gender is either male or female. Similar to this</p>
  
  <pre><code>user_id  country  gender
  1        japan    male
  2        peru     female
  3        japan    female
  4        fiji     male
  5        peru     female
  </code></pre>
  
  <p>I want to run one query to return a unique list of countries, the total number of <code>user_id</code>'s for the country and the male and female numbers for each country. so the results would look like:</p>
  
  <pre><code>country total male female
  peru    2     NUL  2
  japan   2     1    1
  fiji    1     1    NUL
  </code></pre>
  
  <p>I have tried a number of differnet queries but can't return all the results in one query and don't want to use 2 queries if possible.</p>
  
  <p>this is my last try - I know its a mess but I was trying anything:</p>
  
  <pre><code>SELECT DISTINCT a.country AS country, COUNT(b.gender) AS male, COUNT(c.gender) AS     female, COUNT(a.user_id) AS total
    FROM userattributes as a
    LEFT JOIN userattributes as b ON a.user_id=b.user_id
    LEFT JOIN userattributes as c ON a.user_id=c.user_id
    WHERE b.gender='male' AND c.gender='female'
    GROUP BY country;
  </code></pre>
  
  <p>Can anyone do the above in one <code>SELECT</code>?</p>
  
  <p>thx</p>