I have the following information I need to get the total but by country mysql
I have tried with this query
SELECT COUNT(PAIS) AS TOTAL_PAIS
FROM TABLA
WHERE NUMBER <> '-1'
AND NUMBER NOT LIKE '569%'
AND FECHA = '20181115'
and the result is:
**SIN COINCIDENCIAS**
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Argentina
Brazil
Canada
Canada
Colombia
Colombia
Colombia
Colombia
Colombia
Costa Rica
Costa Rica
Costa Rica
Costa Rica
Dominican Republic
France
France
France
France
France
France
Guatemala
Iceland
Iceland
Italy
Mexico
Mexico
Netherlands
Netherlands
Paraguay
Peru
Peru
Peru
Peru
Peru
Peru
Peru
Peru
Peru
Peru
Peru
Russian Federation
Spain
Spain
Spain
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
United States
Uruguay
Uruguay
Uruguay
Uruguay
The result I want to get:
TOTAL COUNTRIES: 19
What you want to do can be done with the '
distinct
' command, I also recommend always using the commandORDER BY
together with this, because if the records are not ordered, it does not make the distinction correctlyTo get only one record per country
I hope it's what you want
At the end you should do
GROUP BY
country since when using an aggregation function you need toCOUNT()
group the data resultthen put
With that it will show the list of countries and their total but grouping by those records that belong to the same country; That is, instead of showing 20 records that say Argentina, it will only show one and in front of the result of the function,
COUNT
for example, 20He
group by
groups them without showing duplicates, andcount(pais)
he will show you the total for each group of countries.