how to use distinct more than 2 fields? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

how to use distinct more than 2 fields?

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-01 : 13:23:13

I have a single talbe like below. I have to count distinct record for each fields. I used following script but do not getting exact result.
Anybody can help?

CREATE TABLE #tbl1
(
Field1 VARCHAR(32),
Field2 VARCHAR(32),
Field3 vARCHAR(32),
Field4 vARCHAR(32),

)

INSERT INTO #tbl1 VALUES('A101','x','x','')
INSERT INTO #tbl1 VALUES('A102','x','x','')
INSERT INTO #tbl1 VALUES('A103','x','x','x')
INSERT INTO #tbl1 VALUES('A104','x','y','x')
INSERT INTO #tbl1 VALUES('A105','x','','y')
INSERT INTO #tbl1 VALUES('A106','y','','')
INSERT INTO #tbl1 VALUES('A107','y','','')
INSERT INTO #tbl1 VALUES('A108','y','','')
INSERT INTO #tbl1 VALUES('A109','y','x','')
INSERT INTO #tbl1 VALUES('A110','y','y','y')

SELECT
count(distinct field1) as Field1
,count(distinct field2) as Field2
,count (distinct field3) as Field3
,count (distinct field4) as Field4
FROM #tbl1
WHERE Field2='x' and field3='x' and field4='x'

Drop table #tbl1

OUT PUT Should be like this :
Field1 Field2 Field3 Field4
10 5 4 2

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-01 : 13:52:02

Your formulas are not doing what you expect they are doing...

SELECT
count(field1) as Field1
,Sum( Case when Field2 = 'x' then 1 end) as Field2
,count ( Case when Field3 = 'x' then 1 end) as Field3
,count ( Case when Field4 = 'x' then 1 end) as Field4
FROM #tbl1

Count or Sum work in this case, I just was showing you some different ways.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-03-01 : 14:00:00

Thanks, great job.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-01 : 14:52:26

Not a problem.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 00:21:11

If you want another method:

SELECT (SELECT COUNT(*) FROM tbl1 WHERE 1 = 1)        AS Field1,
       (SELECT COUNT(*) FROM tbl1 WHERE Field2 = 'x') AS Field2,
       (SELECT COUNT(*) FROM tbl1 WHERE Field3 = 'x') AS Field3,
       (SELECT COUNT(*) FROM tbl1 WHERE Field4 = 'x') AS Field4

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 01:42:40

quote:

Originally posted by ms65g

If you want another method:

SELECT (SELECT COUNT(*) FROM tbl1 WHERE 1 = 1)        AS Field1,
       (SELECT COUNT(*) FROM tbl1 WHERE Field2 = 'x') AS Field2,
       (SELECT COUNT(*) FROM tbl1 WHERE Field3 = 'x') AS Field3,
       (SELECT COUNT(*) FROM tbl1 WHERE Field4 = 'x') AS Field4

This will slow down the exection time. Run this with 10K set of data.
There is no wrong in suggesting the another method which is giving the same rsult. But dont suggest the queries that cause performance issues.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources