Query grouped with different counters

SELECT pais,
   sum(case when sexo = 'M' then 1 else 0 end) Masculino,
   sum(case when sexo = 'F' then 1 else 0 end) Femenino
FROM personas
GROUP BY pais;

And he will return you

+----------+----------+----------+
| pais     | Masculino| Femenino |
+----------+----------+----------+
| Venezuela| 0        |        2 |
| Colombia | 3        |        1 |
+----------+----------+----------+

To obtain the result as you want it in a table you can do it with subqueries:

SELECT pais, 
(SELECT COUNT(*) FROM Personas P1 WHERE P1.pais = P.pais AND sexo = 'F') AS Femenino,
(SELECT COUNT(*) FROM Personas P1 WHERE P1.pais = P.pais AND sexo = 'M') AS Masculino
FROM Personas P
GROUP BY Pais

The result is the following:

pais                      Femenino    Masculino
------------------------- ----------- -----------
BRASIL                    2           0
COLOMBIA                  3           1
VENEZUELA                 2           1

This query is practically the same as @sioesi, only that it uses IF

SELECT pais AS Pais,
   SUM(IF(sexo = 'M', 1, 0)) AS Masculino,
   SUM(IF(sexo = 'F', 1, 0)) AS Femenino
FROM personas
GROUP BY pais
ORDER BY pais ASC

The result will be:

+-----------+-----------+----------+
| Pais      | Masculino | Femenino |
+-----------+-----------+----------+
| BRASIL    |         0 |        2 |
| COLOMBIA  |         1 |        3 |
| VENEZUELA |         1 |        2 |
+-----------+-----------+----------+

  

Note : For the query to work correctly, the pais column must have a case insensitive match, for example latin1_general_ci .

To add to the excellent responses of @sioesi and @Marcos Gallardo, I leave you another option that is a little more compact, but always using a standard SQL that can be used in other databases as well and that is completely equivalent in performance to your answers. The difference is simply that I use the function COUNT instead of SUM to not have to write an expression ELSE :

select pais,
       count(case when sexo = 'M' then 1 end) as masculino,
       count(case when sexo = 'F' then 1 end) as femenino
  from personas
 group by pais

What I do suggest is to avoid using sub queries for this problem. It may seem the most obvious and simple way to execute the sentence, but it is not very efficient and it is not necessary.

Additional note

Even if it works, I suggest you avoid using the double quotes to delimit strings like M and F . It is not standard to do it that way. Rather, it favors the use of single quotes, as indeed all have done in the answers published to your question.

Use this query

SELECT p.pais, count(f.sexo) as femenino, count(m.sexo) as masculino
FROM personas p
LEFT JOIN personas m on  (m.pais = p.pais and m.sexo = 'M')
LEFT JOIN personas f on  (f.pais = p.pais and f.sexo = 'F')
GROUP BY p.pais