HAVING clause: at least one of the ungrouped values is X

Question

Example table:

Col1 | Col2
A    | Apple
A    | Banana
B    | Apple
C    | Banana

Output:

A

I want to get all values of Col1 which have more than one entry and at least one with Banana.

I tried to use GROUP BY:

SELECT Col1
FROM Table
GROUP BY Col1
HAVING count(*) > 1
AND ??? some kind of ONEOF(Col2) = 'Banana'

How to rephrase the HAVING clause that my query works?

Answer 1

Use conditional aggregation:

SELECT Col1
FROM Table
GROUP BY Col1
HAVING COUNT(DISTINCT col2) > 1 AND 
       COUNT(CASE WHEN col2 = 'Banana' THEN 1 END) >= 1

You can conditionally check for Col1 groups having at least one 'Banana' value using COUNT with CASE expression inside it.

Please note that the first COUNT has to use DISTINCT, so that groups with at least two different Col1 values are detected. If by having more than one entry you mean also rows having the same Col2 values repeated more than one time, then you can skip DISTINCT.

Answer 2

SELECT Col1
FROM Table
GROUP BY Col1
HAVING count(*) > 1
AND Col1 in (select distinct Col1 from Table where Col2 = 'Banana');

Answer 3

Here is a simple approach:

SELECT Col1
FROM table
GROUP BY Col1
HAVING COUNT(DISTINCT CASE WHEN col2= 'Banana' THEN 1 ELSE 2 END) = 2

Answer 4

Try this,

declare @t table(Col1 varchar(20), Col2 varchar(20))
insert into @t values('A','Apple')
,('A','Banana'),('B','Apple'),('C','Banana')

select col1 from @t A
where exists
(select col1 from @t B where a.col1=b.col1 and b.Col2='Banana')
group by col1
having count(*)>1