Selecting count of distinct items

  • Thread starter Thread starter Paul Brown
  • Start date Start date
P

Paul Brown

I have a single table containing information about people such as forenames,
surnames, age, etc. I want to create a query that returns two columns of
information: a distinct list of surnames, and a count of how many times each
distinct surname appears in the main table.

Each of these elements is easy enough to achieve on its own with a SELECT
DISTINCT or SELECT COUNT(DISTINCT) style query, but how can I combine them
to get what I want?
 
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[Surname],
Count(*) AS [Instances]
FROM
[Your Table]
GROUP BY
[Your Table].[Surname]
 
Brian Camire said:
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[Surname],
Count(*) AS [Instances]
FROM
[Your Table]
GROUP BY
[Your Table].[Surname]
Click to expand...

Many thanks Brian, that works a treat!
 
Back
Top