How do I select distinct customers who have both attributes 'S' and 'P' in SQL?

Question

In a SQL table I have two fields among many others: CustomerID and TransactionType. CustomerID is a unique number that can be repeated with each transaction. Each transaction is either 'S' or 'P'.

I want to select the distinct customerIDs that have both 'S' and 'P' in SQL. So in the table above my expected return values are 100 and 102. How can I do this?

Answer 1

You can use aggregation for this:

select customerid
from your_table
where transactionType in ('S', 'P')
group by customerid
having count(distinct transactionType) = 2;

You can lose the where clause if the transaction type can only be 'S' or 'P' (not even null).