count the compare number - 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 the compare number

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2007-06-13 : 16:36:14

I need to compare and count the address of 2 from two table that havong count > 1. This is my statement, but it will a bit more help.

select count(tbl) from
(select address + city+ state+ cast(std_zip as varchar(5) as tbl from tbl1 ) as tblddb

where
tbl=(select address + city+ state+ + cast(std_zip as varchar(5) as tb2 from tbl2)
having count(tbl) > 1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-13 : 16:55:10

I'd suggest that you not concatenate all those string together.

select 
	address + city+ state+ cast(std_zip as varchar(5)) as tbl 
from 
	tbl1
group by  
	address + city+ state+ cast(std_zip as varchar(5))
having 
	count(*) > 1
	
	
select 
	address,
	city,
	state,
	cast(std_zip as varchar(5)) AS zip
from 
	tbl1
group by  
	address,
	city,
	state,
	cast(std_zip as varchar(5))
having 
	count(*) > 1

hai
Yak Posting Veteran

84 Posts

Posted - 2007-06-13 : 17:10:32

Hi Lamprey,

Am I missing something from your code...is there a where to compare these 2 table?

thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-13 : 18:25:27

Opps, I missed that. Try something like this (I didn't execute it, but I think I'll work):

select 
	tbl1.address,
	tbl1.city,
	tbl1.state,
	cast(tbl1.std_zip as varchar(5)) AS zip
from 
	tbl1
left outer join
	tabl2
	ON tbl1.address = tbl2.address
	AND tbl1.city = tbl2.city
	AND tbl1.state = tbl2.state
	AND cast(tbl1.std_zip as varchar(5)) = cast(tbl2.std_zip as varchar(5))
group by  
	tbl1.address,
	tbl1.city,
	tbl1.state,
	cast(tbl1.std_zip as varchar(5))
having 
	count(*) > 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-14 : 09:20:01

Also when you concatenate columns, make sure you handle NULL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources