count of non unique rows... - 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 2000 Forums

SQL Server Development (2000)

count of non unique rows...

Author

Topic

eddie
Starting Member

45 Posts

Posted - 2002-02-08 : 16:58:59

I have data which looks like the following

column1 column2 column3 column4
1 er 4 5
2 er 5 6
1 er 6 7

I want to return a count of the number of rows where the combination of column1 and column2 are not unique to the table. This count will need to be put into a variable. Everything I have tried so far returns several counts so if there are 6 duplicate rows, it returns 2 six times which doesn't work for putting it into a variable. I need one number so that I can check if it isn't equal to 0 then there is a problem in the data.

Yes, I know to use unique indexes, we have them, we are trying to check for problems in converted data.

Thanks,
Eddie

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-08 : 17:05:55

DECLARE @duprows int
SELECT @duprows=Count(*) FROM
(SELECT col1, col2 FROM myTable GROUP BY col1, col2 HAVING Count(*)>1) A
SELECT @duprows

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-10 : 05:02:21

Rob's solution is by far the most straightforward way to get your desired results. The alternate solution shown below may be useful if you also needed a queryable frequency distribution of your unique values. This example involves putting the counts and unique values into a TABLE variable (TABLE variables are a new feature of SQL Server 2000).

DECLARE @mycount INT
DECLARE @mytable TABLE (cnt INT, col1 INT, col2 CHAR(2))
INSERT INTO @mytable SELECT COUNT(*) AS cnt, col1, col2 FROM EDDIES_TABLE GROUP BY col1, col2 HAVING COUNT(*) > 1
SELECT @mycount = COUNT(*) FROM @mytable
SELECT @mycount
SELECT * FROM @mytable

Edited by - Spyder on 02/10/2002 05:11:27

eddie
Starting Member

45 Posts

Posted - 2002-02-11 : 08:37:23

Rob,

I get an error of incorrect syntax near keywork 'from' when I run your query. I set mine up exactly like yours. ALso, what is the A used for at the end of line 3?

THanks,
Eddie

Nazim
A custom title

1408 Posts

Posted - 2002-02-11 : 08:51:25

Therez nothing wrong with Rob's solution. you might have omiited something . by your question it looks you have ommitted A. which is compulsory in this case.

A is basicalling Aliasing the source of the table.

to give a example:
select * From A works coz the source of the query is A.
similary
select * from (select * from tablename) K will work coz the source for the query is K which basically contains the result of inner query. if you omit aliasing the inner query with k the main query doesnt know how to identify the inner query e select * from (select * from tablename) and so the error.

hope i make sense. might be Rob will come and explain in lot more detail

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

eddie
Starting Member

45 Posts

Posted - 2002-02-11 : 10:47:01

Ok..sorry...not sure what I did the first time but it works great now.

Thanks!!

Eddie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources