Query to show how many distinct records on column B for each record on Column A using MS Access

Question

I am having a hard time to write the SQL query to do

something like, how many distinct record on column B for each record on Column A

Let's say: Column A,,,,,,,,,,,,,,,,, Column B

             A                   1

             A                   1      would return 3 for A (3 distinct records 1,2,3)

             A                   2

             A                   3

             B                   4      Would return 2 for B (4,5)

             B                   4

             B                   5

             C                   6      Would return 1 for C

             C                   6

it will be imported to a datagridview to show the result

Column 1---------------Column 2

   A                   3

   B                   2

   C                   1

column 2 being the result of the query

I don't know if it is easy and I don't get it or complicated but I can't figure out.

I have tried Group By, Distinct etc... I get either not what I want or error like no value given....

Thank you.

After some research I found this query that seems to return the results expected.

SELECT ColA, COUNT(COLB) FROM (SELECT ColA, ColB FROM Table Where blablabla GROUP BY ColA,ColB) GROUP BY ColA"

Welcome to StackOverflow! Looks like you have found the answer yourself. Please move the part from `After some research ....` to [your own answer below](http://stackoverflow.com/a/41205521/1905949) and mark your answer as accepted. Answers shouldn't be in the question. — ekad, Jan 08 '17 at 02:00

sidesquare · Answer 1 · 2016-12-18T00:17:30.787

0

You could try something like:

SELECT ColA, Count(ColB)
FROM MyTable (NOLOCK)
GROUPBY ColA

edited Dec 18 '16 at 00:17

answered Dec 18 '16 at 00:14

sidesquare

1
3

Thank you very much it is working, I thought it would have been more complicated. – Julien7377 Dec 18 '16 at 00:31
Could be but check to make sure, Senthil solution looks good after reviewing: COUNT(DISTINCT colB) – sidesquare Dec 18 '16 at 00:59

score 0 · Answer 2 · answered Dec 18 '16 at 00:23

0

The sql query is select colA, colB, COUNT(DISTINCT colB) from t1 group by colA

You can access in SQL fiddle.

http://sqlfiddle.com/#!9/2c84d8/7

answered Dec 18 '16 at 00:23

Senthil

2,156
1
14
19

score 0 · Answer 3 · edited May 23 '17 at 12:07

0

Actually, if what you're looking to partition the counts try: over (Partition by)

Partition Function COUNT() OVER possible using DISTINCT

edited May 23 '17 at 12:07

Community

1
1

answered Dec 18 '16 at 00:36

sidesquare

1
3

Query to show how many distinct records on column B for each record on Column A using MS Access

3 Answers3