Count Distinct Values

[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

 

[Ken Snell]

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;

 

[Duane Hookom]

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.

 

[Ken Snell]

Sorry -- typo:

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