Re: Doing a conditional group by

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Steve Kass (skass_at_drew.edu)
Date: 04/07/04 

Date: Tue, 6 Apr 2004 22:41:02 -0700

Jim,

  If I understand you, you want to know how many different col4 values there
are for each col1, col2, col3 having more than one different col4 value
associated with it, which could be done with this:

select count(distinct col4) as cnt, col1, col2, col3
from #test
group by col1, col2, col3
having count(distinct col4) > 1

Steve Kass
Drew University

"Jim L" <vbsql2k@hotmail.com> wrote in message
news:143740f2.0404061803.799e2c62@posting.google.com...
> Using SQLServer 2k
>
> Hi
> I am trying to find duplicate records by grouping by certain columns
> in a table. The query below gets me close to what I need but there is
> an additional condition I need to check for but am at a loss on how I
> to do it.
>
> Based on the DDL below, Everything with matching col1, col2, col3 is a
> duplicate, EXCEPT if the COl4 value is the same. So adding col4 to the
> group by returns bad results. More comments inline below.
>
> Thanks for any assistance,
> Jim
>
>
>
> create table #test
> (
> addressID int identity,
> col1 varchar (10),
> col2 varchar (20),
> col3 varchar(30),
> col4 char (10)
> )
>
>
> insert into #test values('1','2','3','4')
> insert into #test values('1','2','3','4')
> insert into #test values('1','2','3','6')
> insert into #test values('1','2','3','2')
> insert into #test values('1','2','3','4')
> insert into #test values('2','2','3','4')
> insert into #test values('3','2','3','4')
> insert into #test values('5','2','3','2')
> insert into #test values('3','2','3','2')
> insert into #test values('3','2','3','3')
>
>
> select count(*) as 'Dups', col1, col2, col3 from #test
> group by col1,col2,col3
> having count(*) > 1
>
>
> --Query above returns this
>
> Dups col1 col2 col3
> ----------- ---------- --------------------
> ------------------------------
> 5 1 2 3
> 3 3 2 3
>
>
> --Desired result is (exclude 2 rows with col4 value of '4')
>
> Dups col1 col2 col3
> ----------- ---------- --------------------
> ------------------------------
> 3 1 2 3
> 3 3 2 3