asked on

SQL Query to Count Entries Per Column Value

My SQL table has two fields, id and tableName as can be seen in my CREATE statement below. I want to query the table, counting the number of id's per distinct tableName. If there were a total of 120 entries in the table, my report might look something like this...

# IDs Per Table tableName
----------------------------------------------------------
15 ZipCode
100 Race
5 Gender

I don't want to list every entry; just the distinct entries, and the number of entries for that distinct, unique tableName entry.

Here's my current SQL query...

SELECT count(tableName) as "# IDs Per Table" , tableName FROM (SELECT tableName FROM STD_CheckID GROUP BY tableName) ORDER BY tableName

What it produces looks more like this...

# IDs Per Table tableName
----------------------------------------------------------
120 ZipCode
120 Race
120 Gender

Can someone help?

CREATE TABLE STD_CHECKID
(    id NUMERIC(20) not null, tableName VARCHAR(200),
    CONSTRAINT pk_STD_CHECKID PRIMARY KEY (id,tableName) USING INDEX TABLESPACE sdsndx
) TABLESPACE sdstab;

Open in new window

ASKER CERTIFIED SOLUTION