Count nested records in SQL query

Question

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:

Gordon Linoff · Accepted Answer · 2019-12-18 12:33:59Z

3

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;

answered Dec 18, 2019 at 12:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count nested records in SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related