select in with group by - 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)

select in with group by

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-04-20 : 14:54:07

Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'from'.

-- seems pretty strait forward
-- give me all the duplicates for a person
-- where one of the records has the ssn

select count(*), isnull(firstname,'') + isnull(middlename,'') + isnull(lastname,'') as personname
from source_system_contact s
where
not firstname is null
and not middlename is null
and not lastname is null
and (isnull(firstname,'') + isnull(middlename,'') + isnull(lastname,'')) IN
(
select isnull(firstname,'') + isnull(middlename,'') + isnull(lastname,'') as personname
from source_system_contact s
where NOT ssn is null
)
from source_system_contact s
group by isnull(firstname,'') + isnull(middlename,'') + isnull(lastname,'')
having count(*) > 1

You can do anything at www.zombo.com

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-04-20 : 15:22:25

wow that was stupid
found the from/where clause problem

You can do anything at www.zombo.com

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-20 : 20:01:05

how about (as you are excluding null entries)

select count(*), firstname + middlename + lastname as personname
from source_system_contact s
where firstname is not null
and middlename is not null
and lastname is not null
from source_system_contact s
group by firstname, middlename, lastname
having count(*) > 1
and sum(case when ssn is not null then 1 else 0 end) > 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-04-20 : 20:20:58

I LIKE IT!!! why am I thinking of cable guy when writing that... do you need a friend?

You can do anything at www.zombo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources