I need to make a query in MYSQL, I have the following data
COLUM1 FECHA PAIS
0 2018-11-13
0 2018-11-13 Reino Unido
0 2018-11-13
1 2018-11-13 Colombia
0 2018-11-13
1 2018-11-13 Peru
0 2018-11-13
0 2018-11-13 Espana
1 2018-11-13
0 2018-11-13 Colombia
0 2018-11-13
0 2018-11-13 USA
0 2018-11-13
1 2018-11-13 Brasil
0 2018-11-13
0 2018-11-13 Uruguay
0 2018-11-13
0 2018-11-13 Peru
0 2018-11-13
0 2018-11-13
0 2018-11-13 Costa Rica
0 2018-11-13 Costa Rica
0 2018-11-13
1 2018-11-13
0 2018-11-13
0 2018-11-13 USA
0 2018-11-13
0 2018-11-13 Peru
1 2018-11-13
0 2018-11-13 USA
0 2018-11-13
0 2018-11-13 USA
0 2018-11-13
0 2018-11-13 USA
1 2018-11-13
0 2018-11-13
0 2018-11-13 USA
0 2018-11-13
0 2018-11-13 Costa Rica
0 2018-11-13
0 2018-11-13 Argentina
0 2018-11-13
0 2018-11-13 Argentina
0 2018-11-13
0 2018-11-13 Paraguay
0 2018-11-13
0 2018-11-13 Peru
0 2018-11-13
0 2018-11-13 Colombia
1 2018-11-13
1 2018-11-13
1 2018-11-13 Argentina
0 2018-11-13
0 2018-11-13 Peru
0 2018-11-13
0 2018-11-13 Colombia
In the column COLUM1
you can see 0
and 1
where he 0
represents EXITOSO
and1
NO EXITOSO
What I need to bring with me is the following:
- The total
EXITOSO
for each country - The total
NO EXITOSO
for each country
Totals can be obtained in different queries:
To put the number of successful and unsuccessful in the same row you have to do a
JOIN
between the two subqueries. If all the countries appear in the two subqueries, a join can be made between the two tables without data loss. But by doing this, if a country appears only in one of the two subqueries, that country will not appear in the final result.To get all the results, whether the country is on one side, the other, or both, we would have to use a
FULL JOIN
. But Mysql doesn't have FULL JOIN. In an article I found that it can be simulated with theLEFT
andRIGHT JOIN
that are below.