select distinct and how many

[sevex]

Is it possible to do a SELECT DISTINCT, to select all distinct records, but also select how many of each distinct record there are?

table data like:
1, cat
2, dog
3, cat
4, dog
5, dog

and get a recordset with:
cat, 2
dog, 3

thanks in advance for any help.

 

[sevex]

oh i just thought of it.

distinct(field) as mydistinctfield, count(field) as fieldcount from mytable group by mydistinctfield?

I can't test it out yet cause I'm waiting for datasources...

 

[blom0344]

Hello Sevex,

You gave yourself the answer already, but why not create the table from your example (with the 5 records) and check it out. The principle is correct or not regardless if you apply it to 5 or 100.000 records from a source.
Be aware that this counting only works if you only select 1 field to count. Select 2 fields and you will aggregate over these two fields.