Count SQL Query Records

Question

I have a problem that I need to identify how many companies participated in a given project number. For example, identify which companies participated in exactly 2 projects.

Given the base below:

id company project  
1 company1 project1
2 company2 project1
3 company3 project2
4 company1 project2
5 company2 project2
6 company2 project3
7 company3 project1
8 company1 project1
9 company2 project2
10 company1 project2

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

company1
company2

GMB · Accepted Answer · 2019-12-16 22:33:43Z

3

You can use aggregate by company and filter with a having clause:

select company
from mytable
group by company
having count(project) = 2

If the same project may occur twice for a given company, then you want count(distinct project) instead of count(project).

edited Dec 16, 2019 at 22:33

answered Dec 16, 2019 at 22:32

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2019-12-16 22:31:50Z

2

You use group by and having:

select company
from t
group by company
having count(distinct project) = 2;

answered Dec 16, 2019 at 22:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Count SQL Query Records

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related