How to count unique values?

Is there a way to do this without loops? I remember doing it before but cant remember the exact command line.

Lets say i have column called 'cat'

Values
Cat1
Cat2
Cat1
Cat3
Cat1
Cat3

how do i list all of the above with distinct and also to count them.

eg:
Cat1 = 3
Cat2 = 1
Cat3 = 2

 

select distinct cat from ...
and then

$numrows=mysql_num_rows($result)

Code (markup):

 

I dont think it will work because distinct will list the item in like

Cat1
Cat2
Cat3

and if we use mysql_num_rows, it will return 3 rows. Isnt it?

 

it will return (count) what you select and if you don't need the cat name but value just replace cat with the field you're interested in.

Sorry I see what you mean ... then it's a nested query I am afraid
Select the distinct values and :

while $row=mysql_fetch_assoc($result)
{
$cat=$row["cat"];
$result2=mysql_query("select cat from ... where cat='$cat'");
$numrows=mysql_num_rows($result2);
}

Code (markup):

The above code is quick and dirty so please re-write it

 

Yeah i know the loop method but i thought there is another quicker way. Are there any?

Hmmp thanks anyway!

 

SELECT Cat, COUNT(*) AS CatCount FROM TABLE GroupBy Cat

 

SELECT cat||' = '||count(cat) FROM TABLE GROUP BY cat

 

distinct just eliminate the duplicate data..

you will retrieve cat's rows and count all data of it and display the column as CatCount

 

maybe:

SELECT COUNT(DISTINCT cat) FROM TABLE;

or:

SELECT COUNT(DISTINCT cat) AS CatCount FROM TABLE;