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

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

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

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

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

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

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

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

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

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.

a simple SQL statement.

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

Hi,

or in one query:

SELECT COUNT(*)

FROM (SELECT DISTINCT soccd FROM test)

Hoping it may help,

Vanderghast, Access MVP

or in one query:

SELECT COUNT(*)

FROM (SELECT DISTINCT soccd FROM test)

Hoping it may help,

Vanderghast, Access MVP

1. counting number of unique entries in a list

2. how can I count number of bold-face recipe entries in Word list?

3. excel - identify and count the number of unique entries in a list

4. LIST ENTRY >> ENABLE DROP DOWN LIST TO ACCEPT MORE THAN 1 ENTRY

5. Counting distinct entries based on meeting month & year criter

6. Counting distinct entries based on meeting month & year criteria

7. Unique entries based on condition - count distinct

8. How do I count how many times each distinct entry in a column appears?

9. counting distinct entries with a qualification

10. SELECT DISTINCT and SUBSTRING combined to dynamically create distinct entries

11. Count repeated entries without typing the entry (large data base)

12. Delete entries and reorder the remaining entries to preserve counting order

13. Counting entries in column C, based on entries in column A

14. Delete entries and reorder the remaining entries to preserve counting o

15. How do I create a list of each distinct entry in a group of co

7 post • Page:**1** of **1**