SQL Count number of people that do not hold a qualification

hoping you can help me as I am going round in circles!

I need to count the number of people that do not hold a certain qualification.

I have tried NOT but this returns all the other qualifications other than the one I wish to count. Whereas I want to specifically count the number of people that do not hold the qualification, i.e. do not have a row entry in the table.

SELECT COUNT(tmp.personnelID) AS Number,
        tmp.QualificationID

FROM(SELECT pa.PersonnelID,
            pq.QualificationID 

FROM dbo.PersonnelActive pa
LEFT JOIN dbo.PersonnelQualifications pq ON pq.PersonnelID = pa.PersonnelID
WHERE NOT pq.QualificationID = 125) tmp

GROUP BY tmp.QualificationID

Any help you guys can give or point me in the right direction would be greatly appreciated.

You should count for personnelID that are not in the personnelID with a QualificationID = 125

SELECT COUNT(pa.personnelID) AS Num,
        pq.QualificationID

FROM PersonnelActive pa
INNER JOIN dbo.PersonnelQualifications pq ON pq.PersonnelID = pa.PersonnelID
where pa.PersonnelID NOT IN ( SELECT pa.PersonnelID

FROM dbo.PersonnelActive pa
INNER JOIN dbo.PersonnelQualifications pq ON pq.PersonnelID = pa.PersonnelID
WHERE  pq.QualificationID = 125
) 

GROUP BY pq.QualificationID

SQL Count number of people that do not hold a qualification

Recommend Tips