Solved

SQL Query to Count Entries Per Column Value

Posted on 2009-04-14
3
634 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:LarryAndro
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 24140106
you don't need the subselect  group by will make a distinct list on its own

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

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 24143144
Yep, agree a group by problem...

SELECT count(*) as "# IDs Per Table" , tableName  
FROM STD_CheckID
GROUP BY tableName
0
 
LVL 1

Author Closing Comment

by:LarryAndro
ID: 31569999
I apologize for not closing this question and for not awarding points until now.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL + Insert Into Table - If Doesnt Exist 9 47
Separate 2 comma delimited columns into separate rows 2 40
tempdb log keep growing 7 33
Stored Proc - Rewrite 42 56
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question