3

Let's say I have a table of (mostly) faithful couples. The couples appear several times in the same table.

You can tell who's faithful because Jack is always with Dianne, Fred is always with Ginger, etc.

However, Tom, Dick, and Harry are each dating Ms Homewrecker. Like this:

Fred        Ginger
Fred        Ginger
Fred        Ginger
Jack        Dianne
Jack        Dianne
Tom         Homewrecker
Dick        Homewrecker
Harry       Homewrecker
Bogie       Bacall
Heathcliff  Catherine
Heathcliff  Catherine
Heathcliff  Catherine
Heathcliff  Catherine
Adam        Steve
Adam        Steve

I can start by GROUPing by column Y:

select X, Y from couples group by Y

which yields:

X           Y
Fred        Ginger
Jack        Dianne
Tom         Homewrecker
Dick        Homewrecker
Harry       Homewrecker
Bogie       Bacall
Heathcliff  Catherine
Adam        Steve

However, I would like to get a third column which shows the total number of different partners each Y has.

That way, faithful couples will show as always having only one partner.

You can spot the homewrecker because they have > 1 partner.

X            Y           Count  
Fred        Ginger         1    
Jack        Dianne         1    
Tom         Homewrecker    3 <--- THERE'S the b***h!!!
Dick        Homewrecker    3 <--- THERE'S the b***h!!!
Harry       Homewrecker    3 <--- THERE'S the b***h!!!
Bogie       Bacall         1    
Heathcliff  Catherine      1
Adam        Steve          1

There must be something I really don't understand, because I've been going blind been trying every combination of SELECT, DISTINCT, GROUP BY, ROLLUP, COUNT and so on.

ekad
  • 14,436
  • 26
  • 44
  • 46
Sliced Bread
  • 158
  • 1
  • 7

2 Answers2

1

Why do you assume that only Ys could have a few dating partners and Xs not ? :)) Anyway, fast (not the execution time of the query, but the time of thinking over the problem )) query (not the best one as I told above):

SELECT DISTINCT t.X, t.Y, ttt.cnt FROM 
          couples t, 
          (SELECT Y, COUNT(*) cnt FROM 
                  (SELECT DISTINCT X, Y FROM couples) tt GROUP BY Y) ttt 
       WHERE t.Y=ttt.Y
Cheery
  • 16,063
  • 42
  • 57
1

This query should do the trick:

SELECT y, COUNT(DISTINCT x) AS partners 
FROM couples 
GROUP BY y 
HAVING partners > 1

It will count the distinct values of X each Y has and then return only those Y who have more than one partner.

If you also want to check the Xs, consider using a UNION SELECT with x and y interchanged (granted no value occurs in both columns at the same time, otherwise you will not be able to determine whether X or Y with that value is having more than one partner):

SELECT y, COUNT(DISTINCT x) AS partners 
FROM couples 
GROUP BY y 
HAVING partners > 1 
UNION SELECT x, COUNT(DISTINCT y) AS partners 
FROM couples 
GROUP BY x 
HAVING partners > 1
the-banana-king
  • 509
  • 2
  • 9