By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Count unique values by group

P: n/a
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!

May 2 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
> 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
May 2 '06 #2

P: n/a
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 ***
May 2 '06 #3

P: n/a
Rich,

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

Mihir

May 2 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.