• 0

[Access] Counting distincts in Access


Question

I've tried everything, I just can't seem to get Access to count distinct values in a column, and then grouping those by another column. I need to count distinct values in column1 and group them by column2.

Basically this is the starting data...

col1 col2

1 xx
2 xx

1 yy
2 yy
3 yy

1 zz

1 dd
2 dd

This is the result I need...

col1 col2

2 xx
3 yy
1 zz
2 dd

I tried doing SELECT DISTINCT COUNT(col1) FROM table GROUP BY col2, but Google apparently says Count Distinct is invalid in Access. :(

Any suggestions? Thanks :)

Link to comment
Share on other sites

9 answers to this question

Recommended Posts

  • 0

I tried that, but I need it to count uniques. I gave a bad example, sorry. :( Basically I don't want it to count duplicate col1 fields while being grouped by col2 fields.

Link to comment
Share on other sites

  • 0

That sql I gave you isnt pulling out col1. Its producing a dynamic field for the count, try running this to see what I mean:

SELECT COUNT(col2) AS [Count of Column 2], col2 FROM <table> GROUP BY col2

Link to comment
Share on other sites

  • 0

That gives me total count of each col2 grouped by col2. Col1 needs to be considered as well.

Here's a little table I drew quickly to give you an idea.

post-41214-1229544901.gif

The final results of it would be:

12345	 3
5400	  3
1111111   3

Does that make sense?

Thanks for the help btw!

Link to comment
Share on other sites

  • 0

Even though I have no idea why you would want this, here is the code (tested on SQL Server 2005, so Access should be fine with it

select T1.Col2, 
		 count(T1.[Col2]) as Total
from
(
select distinct Col2 as Col2, 
		 Col1
from TestTable
group by [Col1], Col2
) as T1
group by T1.[Col2]

Link to comment
Share on other sites

  • 0
What Sql have you currently got?

SELECT COUNT(col2), col2 FROM <table> GROUP BY col2

Works fine here... this is the answer!

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.