Count number of entries that = a list of distinct entries

G

Guest

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
 
G

Guest

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)
 
G

Guest

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

Ofer said:
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


Brett D said:
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
Click to expand...
Click to expand...
 
J

John Spencer

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


Brett D said:
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

Ofer said:
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


Brett D said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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


Brett D said:
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

Ofer said:
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


Brett D said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.

John Spencer said:
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


Brett D said:
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

Ofer said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


or in one query:


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



Hoping it may help,
Vanderghast, Access MVP

Ofer said:
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


Brett D said:
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

Ofer said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Distinct count for each day 2
COUNT and DISTINCT in Access 3
Using word distinct in Count function. 2
Count Distinct - Alias!? 6
Count Distinct Syntax 3
Case of the curious query ; count the codes 2
how do i perform a Count(Distinct) query for 2 different column va 1
select count(distinct(column name)) from table name 1

Top