Subqueries and the use of count(*) >1

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)

Subqueries and the use of count(*) >1

Author

Topic

BarryKeithNoble
Starting Member

9 Posts

Posted - 2007-07-20 : 12:00:00

I am new to SQL coding, so I apologise if the way I'm thinking about pulling out the results is not the SQL way.

Basically I am trying to select all fields of all records of an Address table where there is duplication of records with the same postcodes and House Number. (Suggesting Duplication of Data which I wish to further analyse)
AL10 9YH 10
AL10 9YH 10

The following script selects the first example of a duplicate record, but not the duplicate record or indeed further fields.

Select Postcode, HouseNum, count(*) as 'Num of Duplicates' from Address
group by Postcode, HouseNum having count(*) > 1

I have tried using nested subqueries using the count (*) > 1 but I am failing with the syntax.

Select Address.Postcode, Address.Street, Address.HouseNum, Address.AddressID
From Address
Where Address.postcode IN (select Postcode from Address group by postcode having count(*) > 1)

The above subquery (not nested) code picks up all records with duplicate postcodes and with substitution of postcode for HouseNum, will do the same with house number duplicates, but how do I nest the two statement and pull out the information I am after.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-20 : 12:06:57

After some assumptions, try this:


Select A.* 
from Address A
Join (
	Select Postcode, HouseNum 
	from Address 
	group by Postcode, HouseNum 
	having count(*) > 1 
	) Dup on A.Postcode = Dup.Postcode And A.HouseNum = dup.Housenum

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

BarryKeithNoble
Starting Member

9 Posts

Posted - 2007-07-26 : 13:50:02

Thanks Dinakar. Your coding seems to have done the trick as well as taught me something new (The use of the dup command)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-26 : 13:56:22

Careful Barry, Dup is not a commmand or function, it's the name of
(Select Postcode, HouseNum
from Address
group by Postcode, HouseNum
having count(*) > 1 )

which is then used to link
A.Postcode = DUP.Postcode And A.HouseNum = DUP.Housenum

Jim

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-26 : 14:02:10

Yes, didnt mean to mislead you. As Jim said, I was using it as an alias for the result set of the ~~subquery~~ derived table. Couldnt be creative enough

.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

EDIT:
As Per Tara's Comments, lets get the semantics right.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-26 : 14:03:15

Just for clarification, it is a derived table rather than a subquery. All derived tables must be aliased.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

BarryKeithNoble
Starting Member

9 Posts

Posted - 2007-08-14 : 05:49:16

Thanks for those clarifications, I have only just realised the concept of derived tables, and was not sure how they worked, coding wise. But its making a bit more sense ever day.

Pity there is no duplicate command in SQL though, just how nice would that be, all I'd have to deal with then is the logic side of things, and thats where information output can really become misleading.

Thanks again for all your comments

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-14 : 13:09:54

select d.<col list> from (
Select <col list>, row_count() over (partition by Postcode, HouseNum) as recid from Address
) as d
where d.recid = 2

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

pootle_flump

1064 Posts

Posted - 2007-08-14 : 17:18:58

It is a minor convention but the AS is optional when aliasing columns\ derived tables. I prefer to use AS (as per peso above) since it makes things just that teeny bit more readable IMHO. Omitting it is not wrong - just a style.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources