Count Distinct in a Group By aggregate function in Access 2007 SQL

Question

Hello I have browsed the forum for a while and am asking my first question here. I'm in a bit of a bind and was wondering if I could get some help out. I am using Access 2007 and have not found a good answer to the question on the Net yet.

My data is Diagnostic Codes and CustomerID's and what I am looking for is a why to find the distinct count of CustomerID's for each Diagnostic Code. Ideally in non-Access SQL it would look like this:

SELECT DiagCode, Count(Distinct(CustomerID))
FROM CustomerTable
Group By DiagCode;

I know this is a pretty straightforward question but the answers that I'm finding are either too complicated(multiple aggregate functions) or too simple. Here is an approach I made to solving it but this is returning too many results:

SELECT DiagCode, Count(CustomerID)
FROM CustomerTable
WHERE CustomerID in (SELECT Distinct CustomerID from CustomerTable)
Group By DiagCode;

Hope I'm being clear here like I said my first post and any help is appreciated.

Answer 1

I'm not expert in MS Access and it is quite a long time last time I have written anything for it, but this maybe will work:

SELECT cd.DiagCode, Count(cd.CustomerID)
FROM (select distinct DiagCode, CustomerID from CustomerTable) as cd 
Group By cd.DiagCode;

Answer 2

I had the same question and found a link (now defunct) by the Access Team at Microsoft to have a nice working example of how to accomplish this; which I will also include here below.

---

Data:

Color   Value
Red     5
Green   2
Blue    8
Orange  1
Red     8
Green   6
Blue    2

To get a count of the number of unique colors in the table, you could write a query such as:

SELECT Count(Distinct Color) AS N FROM tblColors

This would return the value 4 as there are four unique colors in the Color field in the table. Unfortunately, the Access Database Engine does not support the Count(Distinct) aggregate. To return this value from an Access table, you would need to use a subquery such as:

SELECT Count(*) AS N
FROM 
(SELECT DISTINCT Color FROM tblColors) AS T;

Now let's say that you also want to include another aggregate value such as a Sum, and want to group by some value, in this case, Color. On SQL Server, you could write this query as:

SELECT Color, Sum(Value) AS Total, Count(Distinct Color) AS N
FROM tblColors
GROUP BY Color

This provides the following results:

Data:

Color   Total   N
Blue    10      1
Green   8       1
Orange  1       1
Red     13      1

---

Now, if you're asking whether or not this should return the value of '1', the answer is yes. As I understand it, the Count(Distinct) here can be used as a test to verify the results of a given query.

If your data is on a server that supports Count(Distinct), you might be able to use a pass-through query to retrieve the results. If you are working with Access data, this becomes a bit more challenging.

Since we used subqueries for the previous query, we'll need to do the same here. The trick however is that we need to use two subqueries as shown in the following SQL:

SELECT C.Color, Sum(C.Value) AS Total, T2.N
FROM
    (SELECT T.Color, Count(T.Color) AS N 
     FROM 
        (SELECT DISTINCT Color, Count(*) AS N 
         FROM tblColors GROUP BY Color) AS T 
    GROUP BY T.Color) AS T2 
INNER JOIN tblColors AS C
ON T2.Color = C.Color
GROUP BY C.Color, T2.N;

Answer 3

This works in Access 2007 and 2010:

select format(sum(bpa_ext_price) /
              (select count(*) from (select distinct ord_num from sales)),
             "standard") AS Avg_Ord_Amt
from   sales