Query Regarding SQL - SQL Server Forums

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)

Query Regarding SQL

Author

Topic

koushikchandra
Starting Member

24 Posts

Posted - 2010-11-11 : 02:57:23

Hi,

I have the following data set and I want yearmonth wise distinct custid count.

col1 |col2 |col3 |custid
201001 | | |AA
|201002 |201003 |AA
201001 | |201003 |BB
201001 | | |AA

i.e. the desired outout is :

YearMonth | CountCustId
201001 | 2
201002 | 1
201003 | 2

Can anyone help me to achieve this.

Regards,
Koushik

Sachin.Nand

2937 Posts

Posted - 2010-11-11 : 03:42:46

I think there should be 2 records for 201001.

YearMonth | CountCustId
201001 | 2
201001 | 1

PBUH

koushikchandra
Starting Member

24 Posts

Posted - 2010-11-11 : 03:59:25

No.

There are two customer ids AA (2 records for 201001) and BB (1 record for 201001). As I have mentioned in the requirement that for any yearmonth i.e. 201001 I want distinct count of custids. Though AA is repeated twice but it will be counted only once for (for distinct count requirement).

Hope this clarifies.

Regards,
Koushik

Sachin.Nand

2937 Posts

Posted - 2010-11-11 : 04:15:03

[code]
declare @t table(col1 varchar(15),col2 varchar(15),col3 varchar(15),custid varchar(15))
insert @t
select '201001' , ' ' , ' ' , 'AA' union all
select '201002' , ' ' ,'201003' , 'AA' union all
select '201001' , ' ' ,'201003' , 'BB' union all
select '201001' , ' ' , ' ' , 'AA'

select col YearMonth,
count(distinct custid)CountCustId from

(
select * from(
select * from @t)u
unpivot
(col for columns in(col1,col2,col3))v

)T where col<>'' group by col
[/code]

Please learn to normalise your tables.Especially to 1 NF form.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources