Count unique values by group

[Mihir]

All,

I have this table:

ClientName City
Ram Mumbai
Ram Cochin
Ram Mumbai
Ram Bangalore
Lakhan Mumbai
Lakhan Bangalore
Lakhan Mumbai

I want to give the output as:
Name Count of Unique Cities
Ram 3
Lakhan 2

Please help!!!!!

I tried the following:

SELECT DISTINCT tTemp.ClientName, Count(tTemp.City) AS Expr1
FROM tTemp
GROUP BY tTemp.ClientName;
It gave me duplicate values too of the city wher eit was repeasted..
pelase help!

thanks a lot!

[PBsoft]

> ClientName City

Ram Mumbai
Ram Cochin
Ram Mumbai
Ram Bangalore
Lakhan Mumbai
Lakhan Bangalore
Lakhan Mumbai

I want to give the output as:
Name Count of Unique Cities
Ram 3
Lakhan 2

I tried the following:

SELECT DISTINCT tTemp.ClientName, Count(tTemp.City) AS Expr1
FROM tTemp
GROUP BY tTemp.ClientName;
It gave me duplicate values too of the city wher eit was repeasted..

SELECT
MIN(ClientName) AS MyClient,
COUNT(City) AS NumCity
FROM (SELECT DISTINCT ClientName, City FROM tbl03)
GROUP BY ClientName

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype: pbsoftsolution

[Rich P]

Hello Mihir,

Try this - just change the table name to your table

SELECT clientname, Count(t1.city) AS CountOfcity
FROM [select clientname, city from tblclients group by clientname,
city]. AS t1
group by clientname Order By ClientName desc
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Mihir]

Rich,

Thanks a lot!~!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Mihir