SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Count(distinct columns)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cindylee
Yak Posting Veteran

Australia
55 Posts

Posted - 03/13/2005 :  23:05:44  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 03/14/2005 :  00:09:57  Show Profile  Reply with Quote
select
	count(distinct x),
	count(distinct y),
	count(distinct z),
	count(distinct d)
from
	tblx


CODO ERGO SUM
Go to Top of Page

cindylee
Yak Posting Veteran

Australia
55 Posts

Posted - 03/14/2005 :  17:49:53  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 03/14/2005 :  17:53:02  Show Profile  Visit tkizer's Homepage  Reply with Quote


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



Tara
Go to Top of Page

cindylee
Yak Posting Veteran

Australia
55 Posts

Posted - 03/14/2005 :  21:59:37  Show Profile  Reply with Quote
Well Now,,
"THAT IS THINKING"

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

dmorris
Starting Member

1 Posts

Posted - 03/04/2014 :  09:20:53  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000