Menu
I have a table called visitors which contains IP and country columns.
Now I want to query the table such that I get unique countries from the table and display the count of rows of that country.
Table:
IP Country
1.1.1.1. xyz
1.2.3.4 xyz
2.2.3.6 abc
3.61.3.69 axy
Now I want the result as :
Country No_Visitors
xyz 2
abc 1
axy 1
I know how to do it by using 2 queries, get the unique country first and then again query the table for the country name. But how can I do it with single query.
994
Learn MySQL from scratch for Data Science and Analytics The syntax to get all table names with the help of SELECT statement. mysql> use test; Database changed mysql> SELECT Table_name as TablesName from information_schema. tables where table_schema = 'test'; Output with the name of the three tables.
To open, right-click a table in the object browser of the Navigator pane and choose Table Inspector from the context menu. The Table Inspector shows information related to the table.
SELECT statementsSELECT column1, column2 FROM table1, table2 WHERE column2='value'; In the above SQL statement: The SELECT clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. To retrieve all columns, use the wild card * (an asterisk).
Introduction to MySQL SELECT statement First, specify one or more columns from which you want to select data after the SELECT keyword. If the select_list has multiple columns, you need to separate them by a comma ( , ). Second, specify the name of the table from which you want to select data after the FROM keyword.
use AGGREGATE FUNCTION COUNT() to get the total number of instances for each country.
SELECT Country, COUNT(IP)
FROM tableName
GROUP BY Country
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
