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

counting distinct entries with groupby

P: n/a
Hi

I have this scenario:

group | id | value
---------------------------------
x | location | QLD
x | location | NSW
x | location | VIC
x | location | VIC
x | location | NSW
x | location | NSW
x | location | NSW
I want the final count to be:

group | id | count
---------------------------
x | location | 3
Basically it is the number of distinct values, relating to that group
and id. I DONT WANT the number in the grouping x | locaion | 7 nor do I
want..
x | location | qld | 1
x | location | vic | 2
x | location | nsw | 4

but i cant get it.// please help.

Aug 7 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
If you want something like a COUNT DISTINCT, maybe you should read some
of Steve Dassin's posts. (Not to worry folks... he's been gone for
years and I doubt he's coming back...)

http://groups.google.com/group/comp....a4ebedd75dded1

Maybe this one is better:

http://groups.google.com/group/comp....94dec49849455f

Aug 7 '06 #2

P: n/a
Where the table is called tTest:-

SELECT a.group, a.id, Count(a.value) AS [count]
FROM [SELECT DISTINCT tTest.group, tTest.id, tTest.value
FROM tTest
GROUP BY tTest.group, tTest.id, tTest.value
]. AS a
GROUP BY a.group, a.id;
This data ...
group id value
------ --- ------
x location QLD
x location NSW
x location VIC
x location NSW
x location VIC
x location NSW
x location NSW

.... gives this result ...
group id count
------ --- ------
x location 3

and this data ...
group id value
------ --- ------
x location QLD
x location NSW
x location VIC
x location NSW
x location VIC
x location NSW
x location NSW
y c a
y c b
y c c
y c b
y c d

.... gives this result ...
group id count
------ --- ------
x location 3
y c 4

--

Terry Kreft
<ja********@gmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...
Hi

I have this scenario:

group | id | value
---------------------------------
x | location | QLD
x | location | NSW
x | location | VIC
x | location | VIC
x | location | NSW
x | location | NSW
x | location | NSW
I want the final count to be:

group | id | count
---------------------------
x | location | 3
Basically it is the number of distinct values, relating to that group
and id. I DONT WANT the number in the grouping x | locaion | 7 nor do I
want..
x | location | qld | 1
x | location | vic | 2
x | location | nsw | 4

but i cant get it.// please help.

Aug 7 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.