...

View Full Version : Finding Unique Records Then Count Them



adze
11-01-2007, 12:46 PM
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!

Fumigator
11-01-2007, 05:08 PM
SELECT nationality, count(*)
FROM table
GROUP BY nationality

aedrin
11-01-2007, 05:09 PM
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

adze
11-07-2007, 10:40 AM
is there an easier way of doing this?

GJay
11-07-2007, 02:33 PM
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.

adze
11-08-2007, 02:26 PM
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!

GJay
11-08-2007, 02:32 PM
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 />";
}

adze
11-08-2007, 02:40 PM
Mate!

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

Cheers

aedrin
11-08-2007, 05:09 PM
That's what me and Fumigator also said.

Fumigator
11-08-2007, 05:45 PM
There must be an easier way....
:rolleyes:

aedrin
11-08-2007, 06:02 PM
I suppose you could say we forgot to include the ability to copy and paste. Shame on us.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum