Distinct count

[B]myTable1
(id) name[/B]
(1) Tom
(2) Jane
(3) Chris
(4) Judy
(5) Mary
(6) Jack
(7) James
(8) Bill

[B]myTable
(pom) kid[/B]
(1)  2
(3)  1
(3)  4
(5)  8

Let’s suppose I have 2 tables like the above.

The code1 below produces the result1 below.

[B]code1[/B]
select name
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null
order by name

[B]result1[/B]
Chris
Chris
Mary
Tom

I can count the number of records in result1 with the code below.

[B]code2[/B]
[COLOR="#FF0000"]select count(*) as count[/COLOR]
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null

[B]result2[/B]
4

Since Chris comes 2 times in result1, I like the code3 and its result3 below.

select [COLOR="#FF0000"]distinct[/COLOR] name
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null
order by name

[B]result3[/B]
Chris
Mary
Tom

Now I have a problem. i.e, How can I count the number of records in result3?

The following code4 is one of my trials for it. but it still says 4 instead of 3.

[B]code4[/B]
select [COLOR="#FF0000"]distinct[/COLOR] count(*) as count
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null

[B]result4[/B]
4

[FONT=verdana]I don’t know if this would work in MySQL, but in SQL Server I would do this:

SELECT COUNT(DISTINCT Name) FROM … etc.

I think that’s standard ANSI syntax, so it should work with most databases.

Mike[/FONT]

Thank you, Mikl. It works fine in MySQL.