Count Distinct Values

  • Thread starter Thread starter Andre Uys
  • Start date Start date
A

Andre Uys

I have a report with count(*) function to count all the records for each
group.

In the same section I need to indicate how many of the values are unique, in
addition to showing the total count.

I.e.
Location Contact Capacity
A John Manager
A Mike Ops Agent
A John Ops Agent

Summary
Contacts = 3 ( I simply use =count(*))
Unique Employees = 2 (I need help on this one)

Thanks

Andre
 
Try this:

SELECT Count(TotalCount.Contact) AS TotalCount,
(SELECT DISTINCT Count(T.Contact) AS UniqueCount
FROM TableName AS T
GROUP BY T.Contact)
FROM TableName;
 
You can create a totals query that groups by Location and Contact. Then
create another query that groups on Location and counts the unique contacts
from the previous totals query. Then add this final query to your report's
record source query and join the Location fields. This will allow you to
place the count of unique contacts in your report.
 
Sorry -- typo:

SELECT Count(TotalCount.Contact) AS TotalCount,
(SELECT DISTINCT Count(T.Contact) AS UniqueCount
FROM TableName AS T)
FROM TableName;
 
Back
Top