ruedi ruedi - 2 months ago 21
SQL Question

How to combine group by, where and logical operators?

I have the following data structure

| Name | Contract Type |
|:------|--------------:|
| Frank | Student |
| Frank | Staff |
| Frank | Staff |
| John | Internship |
| John | Student |
| John | Staff |
| Tim | Internship |
| Tim | Staff |


I want to get the
Names
of employees who have had a student contract and another contract in theire history. My approach was

Select Name, count(Name) from table
where ContractType = ('Student') AND (ContractType = ('Staff') OR ContractType = ('Internship') )
group by Name having count (Name) > 1


The problem here is that I still get all employees. Anyone who can help me get this accomplished?

Answer

Group by the Name and take only those groups having at least once the Student contract and in total more than 1 contract

Select Name
from your_table
group by Name 
having sum(case when ContractType = 'Student' then 1 else 0 end) > 0
   and count(distinct ContractType) > 1