Search code examples
sqldatabasegroup-bycounthaving-clause

Count SQL Query Records

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
Solution

  • 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).