Finding Unique Records Then Count Them

Ok.
So I have a database full of people details.

And I want to extract each unique Nationality, then count how many of the nationality there are.

Make sense??

So i end up with a table like:

English: 23
French: 36
German: 2
etc

Hope you can help!

SELECT nationality, count(*)
FROM table
GROUP BY nationality

You need to use COUNT() and GROUP BY in SQL.

SELECT COUNT(1) AS number, c.name FROM people p, countries c WHERE p.countries_id = c.id GROUP BY c.id, c.name

is there an easier way of doing this?

easier in what way? The answer given is the shortest (in terms of lines of code) and the most straightforward. Understanding aggregate functions (sum, count etc.) and 'group by' will help you a lot in the future, avoiding them isn't really very sensible.

ok, so i am now able to produce a list of all the nationality's without duplicates.

How do i get it to count how many of each there are and output that?

Sorry if this is obvious, i am new to this!

something along the lines of:

$query = 'SELECT nationality, count(*) AS count FROM table GROUP BY nationality';
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {
echo $row['nationality'].': '.$row['count']."<br />";
}

Mate!

You are a total diamond!
Got it working thanks to you!

Cheers

That's what me and Fumigator also said.

There must be an easier way....
:rolleyes:

I suppose you could say we forgot to include the ability to copy and paste. Shame on us.