• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Query to obtain count from one table based on another table

Table 1:

EmpID  PositionID  CompanyID
12          1                123
13          1                 123
14          2                  123
15          3                  123

table 2

PositionID     test1eligibility     test2eligibility      test3eligibility
1                      1                           1                           0
2                       1                           0                          0
3                       0                             0                         1

I want to know how many empID's in table1 are eligible for test1 (that is test1eligibility =1), how many are eligible for test2 and how many are eligible for test3.
so the result i want is:

companyID     test1eligibility     test2eligibility      test3eligibility
123                      3                       2                              1

can you please help with the SQL query?
1 Solution
    , SUM(t2.test1eligibility) AS test1eligibility
    , SUM(t2.test2eligibility) AS test2eligibility
    , SUM(t2.test3eligibility) AS test3eligibility
FROM table1 t1
      INNER JOIN table2 t2
                  ON t1.PositionID = t2.PositionID

Open in new window

{+ edit} nb: because you display zeros and not nulls in table2 don't use count(), better to use sum() in this situation
Angel02Author Commented:
Brilliant idea!
I just had to change boolean to integer for SUM. Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now