I have the following structure of a MySQL first table:
http://www.clubmadam.com/zadatak.jpg
Structure of a MySQL second table:
http://www.clubmadam.com/country.jpg
I need an SQL query to count number of cities in each country and summarise population for all cities in country;
This is what I have so far:
$upit = "SELECT";
$rezultat = mysql_query($upit);
{
}
Can a MySQL handle this, or do I need to also use PHP? How would I do this?
This really is SQL 101 stuff, I suggest you do a lot of reading
SELECT CountryCode,
COUNT(Name) as Cities,
SUM(Population) as Population
FROM <tablename>
GROUP BY CountryCode
# cities in ESP, # cities in UKR etc.) GROUP BY Name will give him duplicated name values.If I understand correctly, this query will give a count of cities based on country:
SELECT COUNT(*) AS CityCount, CountryCode, SUM(Population) AS CountryPopulation
FROM myTableName
GROUP BY CountryCode
Per your comment, here is how you can do it across multiple tables:
SELECT City.COUNT(*) AS CityCount, Country.LocalName, City.SUM(Population) AS CountryPopulation
FROM City, Country
GROUP BY City.CountryCode
This is a simple MySQL aggregation job using the SUM() and COUNT() aggregate funcions:
$sql = 'SELECT
CountryCode,
COUNT(ID) as numCities,
SUM(Population) as totalPopulation
FROM Cities
GROUP BY CountryCode';
$upit = "SELECT COUNT(ID) AS cities, SUM(Population) AS population, ContryCode FROM table GROUP BY CountryCode";
$result = mysql_query($upit);
while ($data = mysql_fetch_object($result)) {
// $data->cities is the number of cities and $data->population the sum of people and $data->CountryCode the country code
}
Count number of cities in Country-code
$upit = "SELECT COUNT(DISTINCT CountryCode) FROM table_name
$num_rows = mysql_num_rows($upit);
echo "$num_rows Rows\n";
Summarize population for all cities in Country-code
Refer @Mark Baker Answer
