Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 count 2 distinct colums

Author  Topic 

rosenrosen
Starting Member

3 Posts
Posted - 2009-01-16 : 15:42:04
I can use
count(distinct A),A from mytable group by A;
to display each unique value of A along with a count. How can I display all unique value pairs of A and B with a count? With this data:

A | B
----------------
dog | cat
dog | rat
bird | cat
dog | cat
dog | cat
bird | cat

I'd be looking for

count | A | B
------------------
3 | dog | cat
2 | bird | cat
1 | dog | rat

bonus points for sorting, thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-01-16 : 15:47:25
count(distinct A) ... group by A will always give you a count of 1, right?

how about:
select A, B, count(*) from mytable group by A, B order by count(*) desc

Be One with the Optimizer
TG
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2009-01-16 : 16:04:13
[code]
Select distinct A,B,Count(*)over(Partition by A,B)as [Count]
from table
Order by [Count]desc
[/code]
Go to Top of Page

rosenrosen
Starting Member

3 Posts
Posted - 2009-01-16 : 17:42:48
Thank you, that worked perfectly.
Go to Top of Page

Nageswar9
Aged Yak Warrior

600 Posts
Posted - 2009-01-16 : 22:49:48
declare @temp table (A varchar(32), B varchar(32))
insert into @temp
select 'dog','cat' union all
select 'dog','rat' union all
select 'bird','cat' union all
select 'dog','cat' union all
select 'dog','cat' union all
select 'bird','cat'

select distinct count(*) as cnt,a,b from @temp group by a,b
order by cnt desc
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts
Posted - 2009-01-16 : 23:50:22
quote:
Originally posted by Nageswar9

declare @temp table (A varchar(32), B varchar(32))
insert into @temp
select 'dog','cat' union all
select 'dog','rat' union all
select 'bird','cat' union all
select 'dog','cat' union all
select 'dog','cat' union all
select 'bird','cat'

select distinct count(*) as cnt,a,b from @temp group by a,b
order by cnt desc


Why are you repeating what TG posted?
Go to Top of Page

Nageswar9
Aged Yak Warrior

600 Posts
Posted - 2009-01-16 : 23:54:37
I just Posted My view
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-16 : 23:55:29
quote:
Originally posted by Nageswar9

I just Posted My view

but why repeat already suggested ones? please dont duplicate same solution
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-01-17 : 08:44:56
actually, it wasn't exactly what I posted. Nageswar9 included an unnecessary DISTINCT. Nageswar9, why force the plan to distinct the values when you know the GROUP BY insures the list will be distinct anyway?

Be One with the Optimizer
TG
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site