sum of count

[tran008]

I think I a bit over my head here, but can some take a look at the code and give me suggestion how to write this better.

thanks

select sum (case when count(*)<> 0 then 1 else 0 end)
from (

select fname + lname+ address + city+ state+ cast(zip,varchar(5)) as tbl from tbl1
union all
select fname + lname+ std_deladr + city+ state+ cast(zip,varchar(5)) as tb1 from tbl2
) tbl1
group by tbl
having count(tbl) > 2

 

[NoCoolHandle]

I am not certain if this is what you are looking for, (the query was hard to follow, but try this)

select tbl from (
select fname + lname+ address + city+ state+ cast(zip,varchar(5)) as tbl from tbl1
union all
select fname + lname+ std_deladr + city+ state+ cast(zip,varchar(5)) as tb1 from tbl2
) tbl1
group by tbl
having count(tbl) > 2

 

[AlexCuse]

What exactly are you trying to see here? Sample data, expected results would be good.

I am thinking you want to see your count of rows duplicated between the two tables, am I correct?

If that is the case, this might work for you (not tested)

[COLOR=blue]select[/color] tbl, [COLOR=#FF00FF]count[/color](tbl)
[COLOR=blue]from[/color]
(
[COLOR=blue]select[/color] fname + lname+ address + city+ state+ [COLOR=#FF00FF]cast[/color](zip [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](5)) [COLOR=blue]as[/color] tbl [COLOR=blue]from[/color] tbl1
union all
[COLOR=blue]select[/color] fname + lname+ std_deladr + city+ state+ [COLOR=#FF00FF]cast[/color](zip [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](5)) [COLOR=blue]as[/color] tb1 [COLOR=blue]from[/color] tbl2
) a
[COLOR=blue]group[/color] [COLOR=blue]by[/color] tbl
[COLOR=blue]having[/color] [COLOR=#FF00FF]count[/color](tbl) > 1

Note the changes I made to your CAST syntax, they are important.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[tran008]

thanks....but I really only want to add the sum of the count up...I just want to see how many record match.

thanks

 

[AlexCuse]

I think you are going to have to explain more clearly what you mean.

Ignorance of certain subjects is a great part of wisdom