Need the count of distincts with multiple columns
-
I have a table in SQL2000 with about 2.9 million records and I need to count the number of distinct records where the first five columns are concatenated. The columns are varchar's of various lengths ranging from 2 to 80 chars each.
I tried this query:
select count(distinct col1+col2+col3+col4+col5)
from table_name
but this did not work. How can I do this?
-
select count(*) from (select distinct orderid, customerid from orders1) as a
-
Thank you.
-
Why not try this. Group by is more efficient than distinct:
select count(*) from table_name group by col1+col2+col3+col4+col5
Regards,
kokyan
Regards,
kokyan 
-
Sorry for the mistake above. Here is the new statement:
select count(*) from (select count(*) as total from table_name group by col1+col2+col3+col4+col5) a
Regards,
kokyan
Regards, kokyan -
In principle I'll rather stick to Allen_Cui solution.
Row 1 ColA='A' and ColB='A'
Row 2 ColA='AA' and ColB=''
set nocount on
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Test]
GO
CREATE TABLE [dbo].[Test] (
[A] [varchar] (50) NOT NULL ,
[varchar] (50) NOT NULL ,
[C] [varchar] (50) NOT NULL ,
[D] [varchar] (50) NOT NULL ,
[E] [varchar] (50) NOT NULL )
GO
insert test values('A','A','A','A','A')
insert test values('A','A','A','A','A')
insert test values('A','A','A','A','A')
insert test values('A','A','A','A','A')
insert test values('AAAAA','','','','')
insert test values('B','B','B','B','B')
insert test values('B','B','B','B','B')
insert test values('BBBBB','','','','')
GO
Select 'A,B,C,D,E',count(*) from (Select distinct A,B,C,D,E from Test Group by a,b,c,d,e) as a
Select 'A+B+C+D+E',count(*) from (Select distinct A+B+C+D+E as col from Test Group by a+b+c+d+e) as a
Select 'group by A+B+C+D+E',count(*) from Test group by A+B+C+D+E
Select 'group by A,B,C,D,E',count(*) from Test group by A,B,C,D,E
select 'count(distinct A+B+C+D+E)',count(distinct A+B+C+D+E) from Test
GO
Set Nocount Off
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply