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 Col1that have more than one record and at least one s Banana.

I tried using GROUP BY:

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

How to rephrase the sentence HAVINGin which 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 groups Col1that have at least one value 'Banana', using COUNTwith an expression CASEinside it.

, COUNT DISTINCT, Col1. , Col2, , 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 it,

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