Sql-server – Find values which occur in every row for every distinct value in other column of the same table

distinctselectsql serversql server 2014

I need a select statement which finds values from Column B which occurs in all distinct values for A. Meaning in my example it would return: x since x occurs in every value of A. (1,2,3 and 4)

I don't know which values are in Column B so I can't specify this in my query. How can I achieve this in SQL Server 2014?

    +---+----+
    | A | B  |
    +---+----+
    | 1 | x  |
    | 2 | x  |
    | 3 | y  |
    | 4 | y  |
    | 3 | x  |
    | 4 | x  |
    +---+----+

Best Answer

Count distinct A grouping by B, and return those where count(distinct A) match.

select B, count(distinct A)
from table_AB
group by B
having count(distinct A) = (select count(distinct A) from table_AB);

 B   A 
=== ===
 x   4

Related Solutions

Postgresql – Find all values not present for other values of other column

If I interpret your question correctly:

"Pick all rows where a has one of two given values, and b only exists in combination with one of them."

You can use any of the standard techniques laid out in the referenced answer.

Select rows where value of second column is not present in first column

Just restrict your base table to the two given a. For instance

SELECT *
FROM   tbl t1
WHERE  a IN (1,2)
AND    NOT EXISTS (
   SELECT 1
   FROM   tbl t2
   WHERE  a IN (1,2)
   AND    t2.b =  t1.b
   AND    t2.a <> t1.a
   );

SQL Fiddle

This also works with duplicates on (a,b). Multiple identical rows would be returned.

Sql-server – Count distinct values of a column and insert them to another table

Try using

SUM(Case WHEN problem Like '%err1%' THEN 1 ELSE 0 END) //for problem count

'%err1%' is a sample error containing letter to rectify which error is this.

Best Answer

Related Solutions

Postgresql – Find all values not present for other values of other column

Sql-server – Count distinct values of a column and insert them to another table

Related Question