CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008group-by
Matt_Bro
Matt_Bro

Reputation: 14502

Comparing columns within a group in sql

I'm very new to SQL so I appologize if this question is difficult to understand.

Let's say I have a table like:

Name    Birthday
Bob     7/18
Bob     7/18
Mark    5/10
Mark    7/5  
Sue     2/1
Joe     1/14
Joe     1/14
Joe     1/2
Jeff    9/16
Jeff    3/20
Jeff    6/13
[...]

I would like to perform a select statement that gives me the Names of the people who have more than one distinct Birthdays.

So, for my example table, the output would be Mark, Joe, and Jeff.

Thanks for your help.

Upvotes: 2

Views: 6596

Answers (4)

Tom H
Tom H

Reputation: 47402

You can do this several different ways. You can either JOIN the table to itself, or you can use the EXISTS clause.

SELECT DISTINCT
    T1.name
FROM
    Some_Table AS T1
WHERE
    EXISTS (SELECT *
            FROM Some_Table T2
            WHERE T2.name = T1.name AND
                  T2.birthday <> T1.birthday)

or

SELECT DISTINCT
    T1.name
FROM
    Some_Table AS T1
INNER JOIN Some_Table AS T2 ON
    T2.name = T1.name AND
    T2.birthday <> T1.birthday

You could also solve this particular problem using the HAVINGclause:

SELECT
    T1.name
FROM
    Some_Table AS T1
GROUP BY
    T1.name
HAVING
    MAX(birthday) <> MIN(birthday)

Upvotes: 0

MatBailie
MatBailie

Reputation: 86808

A re-wording as I understand your problem statement:

All Names where:
- The name has more than one Colour associated to it
- Of those, at least two Colours have different birthday's associated to them

What I'm unsure of is whether it's possible to have two different birthdays for the same colour associated to the same name?


If no, the colour becomes irrelevant, you just want a name with more than 1 different birthday associated to it.

SELECT
  Name
FROM
  yourTable
GROUP BY
  name
HAVING
  COUNT(DISTINCT birthday) > 1


If `yes`, you need to find another record with the same name, but also a different colour AND a different birthday. SELECT Name FROM yourTable WHERE EXISTS (SELECT * FROM yourTable as [lookup] WHERE Name = yourTable.Name AND Birthday yourTable.Birthday AND Colour yourTable.Colour) GROUP BY Name

Upvotes: 3

KM.
KM.

Reputation: 103727

try this:

select
    NAME
    FROM (select
              NAME
              FROM YourTable
              GROUP BY Name,Birthday
              HAVING Count(Name)=1
         ) dt
    GROUP BY Name
    HAVING COUNT(Name)>1

working sample code for SQL Server:

DECLARE @YourTable table (name varchar(10),Birthday varchar(10), FavoriteColor varchar(10))
INSERT @YourTable VALUES ('Bob'  ,   '7/18'   ,    'Blue')
INSERT @YourTable VALUES ('Bob'  ,   '7/18'   ,    'Green')
INSERT @YourTable VALUES ('Mark' ,   '5/10'   ,    'Blue')
INSERT @YourTable VALUES ('Mark' ,   '7/5'    ,    'Green')

select
    NAME
    FROM (select
              NAME
              FROM @YourTable
              GROUP BY Name,Birthday
              HAVING Count(Name)=1
         ) dt
    GROUP BY Name
    HAVING COUNT(Name)>1

OUTPUT:

NAME
----------
Mark

Upvotes: 1

user874687
user874687

Reputation: 

SELECT name FROM table_xyz 
GROUP BY Name, Birthday
HAVING count(*) = 1

Upvotes: 1

Related Questions