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 2000 Forums
 Transact-SQL (2000)
 Count(distinct columns)

Author  Topic 

cindylee
Yak Posting Veteran

55 Posts

Posted - 2005-03-13 : 23:05:44
Hi guys
i need to get count of few distinct columns in my table.
the following query works fine (only when one column is specified)

select count(distinct x)
from tblx

but i want to look for more than one column in distinct..someting like this

select count(distinct x,y,z,d)
from tblx

getting following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.


Any help greatly appreciated
cindy

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-14 : 00:09:57
[code]select
count(distinct x),
count(distinct y),
count(distinct z),
count(distinct d)
from
tblx[/code]

CODO ERGO SUM
Go to Top of Page

cindylee
Yak Posting Veteran

55 Posts

Posted - 2005-03-14 : 17:49:53
Thanks
but i just want one count result.
I dont think we can get a total count for more than one distinct column(s)

Thanks
cindy
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-14 : 17:53:02
[code]

SELECT COUNT(*)
FROM
(
SELECT DISTINCT Column1, Column2
FROM @Table1
) t

[/code]

Tara
Go to Top of Page

cindylee
Yak Posting Veteran

55 Posts

Posted - 2005-03-14 : 21:59:37
Well Now,,
"THAT IS THINKING"

Thanks tara, it works great
Cindy
Go to Top of Page

dmorris
Starting Member

1 Post

Posted - 2014-03-04 : 09:20:53
Another option is to concatenate multiple columns in your count expression

count(distinct cast(col0 as varchar) + '.' + cast(col1 as varchar)) as unique_col0_and_col1

I don't think this is better than using a sub query.
Go to Top of Page
   

- Advertisement -