Count Instances of 3 Coloumns - 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)

Count Instances of 3 Coloumns

Author

Topic

Grinja
Starting Member

5 Posts

Posted - 2008-01-18 : 06:25:49

Hi,
I need to identify where a combination of 3 Columns occurs more than once.

What I have so far (simplified for the post)

SELECT A, B,C
FROM tblX

GROUP BY A,B,C
HAVING COUNT A > 1
AND COUNT B > 1
AND COUNT C > 1

Any help would be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 07:04:23

SELECT A,B,C
FROM tblX
GROUP BY A,B,C
HAVING COUNT(*) > 1

E 12°55'05.25"
N 56°04'39.16"

Grinja
Starting Member

5 Posts

Posted - 2008-01-18 : 07:22:29

Thanks for input... I have tried this but get the same result as my previous code.

Lets say values are as follows:
ColA = Peter
ColB = Smith
ColC = X

I want to identify the records where the combination
Peter Smith X appears more than once.

When I tried HAVING (Count *) I got combination as follows:
Row1 - Peter Smith X
Row2 - Peter Smith Y

Thanks for the help so far.

Grinja
Starting Member

5 Posts

Posted - 2008-01-18 : 07:33:20

I tried this
HAVING COUNT(A + B + C) > 1

I got the same result as HAVING COUNT (*)

If I take the result from the above and use a SELECT as follows:
SELECT A,B,C
WHERE A=Peter
AND B=Smith
AND C=X

I get one row returned ... shouldn't it get more than 1?

I think I'm missing somthing..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 07:49:15

quote:
Originally posted by Grinja

I tried this
HAVING COUNT(A + B + C) > 1

I got the same result as HAVING COUNT (*)

If I take the result from the above and use a SELECT as follows:
SELECT A,B,C
WHERE A=Peter
AND B=Smith
AND C=X

I get one row returned ... shouldn't it get more than 1?

I think I'm missing somthing..

Did you try Peso's query?

Madhivanan

Failing to plan is Planning to fail

Grinja
Starting Member

5 Posts

Posted - 2008-01-18 : 08:25:36

Sorry had a real blonde moment

All the above worked fine . In my original query there are numerous sub queries ... I left a SELECT DISTINCT in one of them.

Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources