Count nested records in SQL query

I have a problem that I need to select all owners who have 2 different companies where each company is on 2 different projects.

For example :

owner  company project  
1     company1 project1
1     company1 project2
1     company2 project2
1     company2 project3
1     company2 project1
2     company1 project1
2     company2 project1
2     company3 project1
2     company3 project2
3     company2 project2
3     company1 project1

I tried to do like this :

select owner from table 
group by owner 
having count(distinct company)>1 and count(distinct project)>1

But it did not work.

How to use a SQL query to solve this problem? For this case above, the query is expected to return:

1

Answer

I have a problem that I need to select all owners who have 2 different companies where each company is on 2 different projects.

Use two levels of aggregation:

select owner
from (select owner, company, count(*) as num_projects
      from t
      group by owner, company
     ) t
where num_projects = 2
group by owner
having count(*) = 2;

This assumes that owner/company/projects are not duplicated. If that is possible, use count(distinct project) in the subquery.

Note: If you mean owners that have at least two companies with at least two projects, then you would tweak this to:

select owner
from (select owner, company, count(*) as num_projects
      from t
      group by owner, company
     ) t
where num_projects >= 2
group by owner
having count(*) >= 2;