SQL, Finding values in column S that map to the same value in column B

Question

I have a single table with several columns. The relevant columns are S and B.

S  |  B
-------
s1 | b1
s2 | b1
s3 | b2
s4 | b3
s5 | b4
s6 | b4
s7 | b5
s7 | b6
s7 | b7
s8 | b7

I would like to get a listing of all S values that map to the same B value and then count the unique B values: So the results from the above table would be as listed below and the count of unique B values would be 3 (b1, b4 and b7)

S  |  B
-------
s1 | b1
s2 | b1
s5 | b4
s6 | b4
s7 | b7
s8 | b7

In an attempt to solve the problem via listing duplicate B values I came across queries that help find duplicates, but that is not exactly what I need.

Can anyone help?

Answer 1

First I would generate a set of data with the distinct counts of each S for each by then join back to your base set of data in source table and limit by the count > 1 from the inline view.

SELECT Z.S, Z.B 
FROM Table Z
INNER JOIN (
  SELECT count(Distinct S) cnt, B 
  FROM table 
  GROUP BY B)  Y
 on Z.B = Y.B
Where Y.cnt > 1

Answer 2

Presuming SQL-Server you can use following queries using the Over-clause:

WITH CTE AS
(
  SELECT S, B, COUNT(*)OVER(PARTITION BY B) AS CntB
  FROM TableName
)
SELECT S, B
FROM CTE 
WHERE CntB > 1
;

WITH CTE AS
(
  SELECT S, B, COUNT(*)OVER(PARTITION BY B) AS CntB
  FROM TableName
)
SELECT CntUniqueB = (SELECT COUNT(DISTINCT B)
                     FROM CTE
                     WHERE CntB > 1)

DEMO