Query grouped with different counters


Good afternoon, I hope you are well. I have a problem with a SQL query that paints to be simple but I think it is not, I have the following table with this information:

id   pais    sexo
== ========  ====
 1 Venezuela F
 2 Venezuela F
 3 Venezuela M
 4 Colombia  F
 5 Colombia  F
 6 Colombia  F
 7 Colombia  M
 8 Brasil    F
 9 Brasil    F

I must make a query, that you return the information to me in this way:

pais      femenino masculino
========= ======== =========
Venezuela        2         1
Colombia         3         1
Brasil           2         0

I have thought something like this:

SELECT count(per1.sexo), count(per2.sexo), per1.pais, per2.pais
FROM 'personas' per1,'personas' per2
per1.sexo = "F"
AND per2.sexo = "M"
group by(per1.pais)

but it is not the solution and it does not work for me, please if you can help me, thank you very much:)

asked by Diego Guerrero 06.12.2016 в 22:12

5 answers

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 |
answered by 06.12.2016 в 22:30

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

The result is the following:

pais                      Femenino    Masculino
------------------------- ----------- -----------
BRASIL                    2           0
COLOMBIA                  3           1
VENEZUELA                 2           1
answered by 06.12.2016 в 22:43

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

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 .

answered by 06.12.2016 в 23:46

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.

answered by 07.12.2016 в 01:35

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
answered by 06.12.2016 в 22:41