How to produce count of distinct records

I am trying to get a count of distinct part numbers from a table where a part could be listed more than one time.  Here is an example table of data:

Master123 | Detail123
Master123 | Detail123
Master123 | Detail456
Master123 | Detail456
Master123 | Detail456
Master123 | Detail789
Master456 | Detail789

I want the count of different "Detail" numbers.  In this case, I want the query to produce 3 as a result for Master123, and 1 for Master456.
dzirkelbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
select id, count(distinct part)
from table
group by id
SujithData ArchitectCommented:
with data as (
select 'Master123' id , 'Detail123' part union all
select 'Master123' id , 'Detail123' part union all
select 'Master123' id , 'Detail456' part union all
select 'Master123' id , 'Detail456' part union all
select 'Master123' id , 'Detail456' part union all
select 'Master123' id , 'Detail789' part union all
select 'Master456' id , 'Detail789' part  
)
select id, count( distinct part)
from data
group by id

Open in new window

dzirkelbAuthor Commented:
Thanks for the fast responses!  I'm using Access 2000, so maybe I have some incorrect syntax as I'm getting an error.  This is the exact query:

SELECT  bomref.fcparent, Count(DISTINCT bomref.fcomponent) AS CountOffcomponent
FROM bomref
GROUP BY bomref.fcparent
HAVING (((bomref.fcparent)="PGMUVCBKRC"));

here is the error:

Syntax error (missing operator) in quyery expression 'Count(DISTINCT bomref.fcomponent)'.
John TsioumprisSoftware & Systems EngineerCommented:
SELECT DISTINCT T.fcparent, Count(T.fcomponent) AS CountOffcomponent
FROM (SELECT DISTINCT fcparent,fcomponent
FROM bomref
GROUP BY fcparent,fcomponent) T

GROUP BY T.fcparent;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SujithData ArchitectCommented:
Nice.  Looks like access doesnt support DISTINCT within the count function.

You dont need the DISTINCT in the outer query though.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.