>>How does SQL Server know that all the values are the same?
>
>when there is a join on a unique key -

In theory this could work. It would require that the Declared key be included in the GROUP BY. But it's still a special case of the general issue, albeit, one that could be handled as an exception by SQL Server.

>all I can say is that VFP does it and doesn't seem to have any trouble with it - this is one (maybe the only one) aspect of VFP's implementation of SQL that I like better than SQL Server ...

This is a situation where VFP's implementation of SQL does not follow the ANSI standard.

BTW, there is a solution for SQL Server using corralated queries:

SELECT st.id, st.name, st.capital,
(SELECT COUNT(*) FROM cities WHERE st_id = st.id) AS NumOfCities
FROM states st
...

-Mike