Count number of entries that = a list of distinct entries

Count number of entries that = a list of distinct entries

Post by QnJldHQgR » Thu, 09 Feb 2006 01:19:31


Hello,
I need to count the number of occurrences for many different entries in a
table. Basically, I have a "category" column (socnm) that contains many
occurrences of several different categories. What I'm hoping to do is select
the DISTINCT rows from the column and then use them to get the count for each
category in the table. I tried:
SELECT count(*) AS [COUNT]
FROM Test
WHERE Test.socnm IN (SELECT DISTINCT [Test].[socnm] FROM Test);
But it just ended up giving me a count equal to the total number of rows. I
want to do a count for distinct socnm(1) and then save/output it then loop
through socnm(N) each time saving/outputting the count for that category. I'm
thinking of this from a linear programming POV.......Can anyone straighten me
out?

Thanks very, very much in advance.
Brett
 
 
 

Count number of entries that = a list of distinct entries

Post by T2Zlc » Thu, 09 Feb 2006 01:38:05

In the Tex=st table there is probably another field that used to combine the
key beside socnm, use this field for the criteria

SELECT count(*) AS [COUNT]
FROM Test
WHERE Test.[OtherFieldName] IN (SELECT Top 1 [Test].[OtherFieldName] FROM
Test As TE Where TE.socnm = Test.socnm)

--
\\// Live Long and Prosper \\//
BS"D

 
 
 

Count number of entries that = a list of distinct entries

Post by QnJldHQgR » Thu, 09 Feb 2006 02:00:31

Thanks for the quick response!
I found a field (soccd) that seems to be numerical identifiers that
correspond to socnm so I used that.

I tried:
SELECT count(*) AS [COUNT]
FROM Test
WHERE Test.[soccd] IN (SELECT Top 1 [Test].[soccd] FROM
Test As TE Where TE.socnm = Test.socnm)

but got the same as before, a count total of 4101 (number of rows in table).

I'm hoping to get a count for the occurrence of each socnm.
e.g. My table Test =
soccd socnm
111 "Disorder #1"
111 "Disorder #1"
222 "Disorder #2"
111 "Disorder #1"
I want as output 3 (for num occurrences of 111) and 1 (for 222).
Thanks very much again!
bd
 
 
 

Count number of entries that = a list of distinct entries

Post by John Spenc » Thu, 09 Feb 2006 02:25:41

Have your tried?

SELECT SocCD,
Count(SocCD) as CountThem
FROM Test
GROUP BY SocCD

Based on your test table that should return two rows as follows.
111 3
222 1
 
 
 

Count number of entries that = a list of distinct entries

Post by T2Zlc » Thu, 09 Feb 2006 02:55:18

One option you can use is in two steps
1. Create a group by query on the test table

SELECT soccd
FROM Test
GROUP BY soccd

2. Create a second query based on the first query

Select Count([soccd]) As CountOfsoccd From QueryName


--
\\// Live Long and Prosper \\//
BS"D
 
 
 

Count number of entries that = a list of distinct entries

Post by QnJldHQgR » Thu, 09 Feb 2006 03:18:27

John that did it! Thanks very much. I can't believe it turned out to be such
a simple SQL statement.

Thanks to both of you for your quick replies and helpful hints.
 
 
 

Count number of entries that = a list of distinct entries

Post by Michel Wal » Thu, 09 Feb 2006 03:29:20

Hi,


or in one query:


SELECT COUNT(*)
FROM (SELECT DISTINCT soccd FROM test)



Hoping it may help,
Vanderghast, Access MVP