how to write this query... count, unique, ??

[Jessica Sant]

So I've got a DB that's got all these codes in it.

I can write
--- select unique CODE from TABLE;
and I get a list of all the different kinds of codes in the table

I can write
--- select count( CODE ) from TABLE;
and I get the number of codes in the whole table.

I can write
--- select count (unique CODE) from TABLE;
and I get the number of differnt codes in the table.

BUT -- how do I write a query that will tell me all the unique codes and how many instances there is of that code in the DB. So I want my result to look like this:

CODE  |   count
------|---------
ABC   |   4
XYZ   |   3
UVW   |   18
DEF   |   1
MNO   |   12

I'm having quite the brain fart on this one.. I can't remember my basic SQL... Thanks!!

[Sreenath Madasu]

You could try

select code, count(code) from table
group by code

[Peter Rooke]

GROUP BY, so:-

SELECT CODE, COUNT(*) as CODE_COUNT
   FROM <TABLE_NAME>
   GROUP BY CODE;

Note - I've called the count(*) column CODE_COUNT as 'count' is a SQL term. It may work with 'count'.
-----
Of complication, despond, and general distress. Are two nulls equal?. I fear both no and yes!

[Michael Matola]

Note that count(*) and count(code) will differ in how they treat nulls. You may or may not care.

--- select count( CODE ) from TABLE;
and I get the number of codes in the whole table.

Another way of thinking of that is "and I get the number of rows on the table where code is not null."