[Resolved] Writing a Join'ed MySQL Query

[OntarioEastern]

Hi All,

I have be struggling with this trying to get the right query to work, but when I try to test it each time it fails. May I have some assistance with this please.

What I want the query to do is get the distinct City, then count the number of records for that City from the database.

I can do this in two steps in PHP with separate queries, but I want to do this more efficent.

What I have for the first part is:

SELECT DISTINCT(CITY) FROM MY_DB

The second statement is:

SELECT COUNT(ID) AS CITYCOUNT FROM MY_DB WHERE CITY = '$city'

What I tried was an INNER JOIN, however my skills on SQL are rather limited.

What I want the end result to be is that when I loop through the records returned, I get the CITY and the number of records there are for that CITY.

Thanks in Advance.

Phil

[Roger_Ramjet]

The answer to this one is 1 query with a GROUP BY and an aggregate function , in this case the COUNT() function. This will group all records with the same city and count them so you'll get a list of distinct cities and their respective counts.

PS: no join needed here

[cheese]

Hi Ontario,

How about something like this;

$query = mysql_query("SELECT City, COUNT(City) AS CityTotal FROM YourTable GROUP BY City ORDER BY City ");

print "<table>";
print "<tr><td>City Name</td><td>Number of Times</td></tr>";
while ($row = mysql_fetch_array($query, MYSQL_NUM))
{
print "<tr><td>" . $row[0]. "</td><td>" . $row[1]. "</td></tr>";
}
print "</table>";

Hope that helps.

Neil.

[OntarioEastern]

Thanks everyone.. That helped and I just learned something new.