Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1646
  • Last Modified:

COUNT(DISTINCT column) syntax for access

Hi,

It seems like the syntax COUNT(DISTINCT column) is not working in Access.

I simply need to count distinct (unique) values in a column. How can I do that in Access?

Thanks!

0
Bear2
Asked:
Bear2
  • 10
  • 4
  • 3
  • +2
1 Solution
 
peter57rCommented:
Select count(*) from (Select distinct fieldname from table)
0
 
dportasCommented:
SELECT COUNT(*)
FROM
 (SELECT DISTINCT column
  FROM tbl
  WHERE column IS NOT NULL
 )  AS t;
0
 
Bear2Author Commented:
Well that works, but my query is a little bit more complex and I'm not sure how to use that syntax in my case.

I need a syntax for:

SELECT Sum(columnA) AS column1 , column2,  COUNT (DISTINCT columnC) AS column3
FROM TableA
GROUP BY column2;

Can you show how to use your syntax for that?

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
How about this:

SELECT Table1.FIELD2, Sum(Table1.FIELD1) AS SumOfFIELD1, First(Table1.FIELD2) AS FirstOfFIELD2, Count(Table1.FIELD3) AS CountOfFIELD3
FROM Table1
GROUP BY Table1.FIELD2;


mx
0
 
Bear2Author Commented:
Unfortunetly that solution don't work because it is just using a simple count() which means that it counts all field3 but what I need is a function only counting distinct values just like COUNT(DISTINCT column) function that is not working in access.

Hope someone can come up with a solution for this one!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... this is how you would Count just FIELD3:

SELECT Table1.FIELD3, Count(Table1.FIELD3) AS F3Count
FROM Table1
GROUP BY Table1.FIELD3;

mx
0
 
Daniel WilsonCommented:
Bear2,

Please post some sample data and the desired result.  Some pretty sharp guys are missing what you're saying ... so an example would help.
0
 
Bear2Author Commented:
Ok let me explain this again.

What I need a solution for is:

SELECT Sum(columnA) AS column1 , column2,  COUNT (DISTINCT columnC) AS column3
FROM TableA
GROUP BY column2;

In this problem columnA is a dollar amount, column2 is vehicle version and columnC is registration number. Each row in table A is a invoice.
A registration number may occur more than once because a vehicle can have more than one invoice.
I want to group the invoices on vehicle versions (column2).

I do not want to count the number of times a registration number is billed.

I do want to sum the invoices (column A) grouped by vehicle version (column2) and count the number of vehicles. Because I need to count the number of vehicles in each vehicle version I only want to count the registration number ONCE. Therefore I want to use the COUNT (DISTINCT columnC) that unfortunately is not working in access.

I really appreciate if someone has a solution for this one in access.

Thanks!




0
 
Daniel WilsonCommented:
OK, taking a stab at sample data ...

ColA       ColB          colC
50           SLT           1F123
25          SLT            1F123
75           TRK            2D345


And the result would look like  ...
75       SLT           2
75       TRK           1

Or ... am I missing something?
0
 
Bear2Author Commented:
Yes that is exactly the result I need from the query.
0
 
Daniel WilsonCommented:
If this doesn't get it, please show example data for which it fails.  For the example I provided it will work.

Select sum(ColumnA), ColumnB, Count(ColumnC)
From MyTable
Group by ColumnB

Open in new window

0
 
dportasCommented:
COUNT(DISTINCT) is going to be rather painful to do in Access because there is no direct equivalent and Access has trouble with correlated subqueries. Try the following:

SELECT t1.column1, t1.column2, NZ(t2.column3,0) AS column3
FROM (SELECT SUM(columnA) AS column1, column2
   FROM TableA
   GROUP BY column2
   )  AS t1 LEFT JOIN (SELECT column2, COUNT(*) AS column3
   FROM
   (SELECT DISTINCT column2, columnC
    FROM TableA
    WHERE columnC IS NOT NULL)t
   GROUP BY column2
   )  AS t2 ON t1.column2 = t2.column2;

BTW, I note that the result suggested by Daniel's sample data is actually COUNT(*) and NOT COUNT(DISTINCT). Hope that's clear.
0
 
Bear2Author Commented:
No I'm really sorry!

Your example:

ColA       ColB          colC
50           SLT           1F123
25          SLT            1F123
75           TRK            2D345


And the result would look like  ...
75       SLT           2
75       TRK           1

Or ... am I missing something?

Yes the result should look like:

75   SLT  1
75 TRK    1

That is because both rows with SLT has the same registration number 1F123.

To extend your example:

ColA       ColB          colC
50           SLT           1F123
25          SLT            1F123
75           TRK            2D345
40           SLT            4D325
80           TRK            2D345
70            TRK           7C765
50             TRK           8C567

The result should be:

115    SLT    2
275    TRK    3

Makes sense? I only want to count unique calues of colC!
 
0
 
dportasCommented:
Did you try my query?
0
 
Bear2Author Commented:
dportas,

I haven't tried your query yet but I will today. I let you know about the result.

Thanks
0
 
Bear2Author Commented:
dportas,

Your solution is working, but the problem is that when trying to save this query in Access the database crashes so I am not able to save it. I am trying to find a solution for that but no success so far. Any suggestion? Can you save that query in Access?

Regards
0
 
Bear2Author Commented:
dportas,

I have tried to modify your query but have not found a solution.

As I wrote your query is giving me the expected result, but I can not save that query because Access gets some kind of performance issue and closes down. Therefore I can not use the query.
I appreciate if you have another solution for this problem or a way to modify your query.

Thanks in advance!
0
 
Daniel WilsonCommented:
Before putting in dportas' code, try doing a Compact & Repair on your DB.

Then put his code in & see if you can save it w/o the crash.
0
 
Bear2Author Commented:
It seem like there is no perfect code for this in Access, but I have got dportas code to work without having my database crashing when saving.

This is what I did:

I deleted all rows in my huge table that the query is running towards except for a few rows.
That made the query to run easier and I was able to save it without a crash.
After that I could import my huge table and run my query with success and also use it as a subquery for other queries.

Thanks for your assistance!
0
 
Bear2Author Commented:
Thanks for a good solution. Unfortunately ACCESS has performance issues with this types of solutions but I got it to work finally.

Thank you!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now