# Acquiring the number of contracts for persons

This script shows the number of contracts for persons. Table Contract has more than 82008 records and Candidate has about 7978.

Could you please suggest improvements for this script?

select ISNULL(CON.ContractCount, 0)
,      CAN.PersoonName
,      ... /* some fields from table Candidate (more than 25) */
from   Candidate CAN
left join (select PersoonID, COUNT(*) ContractCount
from Contract
group by PersoonID) CON on CAN.PersoonID = CON .PersoonID
where CAN.Type = 11

• Hi, can you clarify what's in the "..." ? If it's not too long, please include it anyway. – Kaz Sep 9 '15 at 10:34
• "..." - there have to be more than 25 fields to select – Roman Marusyk Sep 9 '15 at 10:37

## 1 Answer

Sometimes, when doing left-join aggregation with a simple function (count()), it is easier to move the aggregation to be part of the select clause instead of as a subquery on the from clause.

As long as you are only pulling one aggregate from the sub table, it's easy.

Also, I presume there is at least one index on Contract where the PersoonID is the first (or only) column.

select (select count (*)
from Contract
where PersoonID = CAN.PersoonID
) as ContractCount
,      CAN.PersoonName
,      ... /* some fields from table Candidate (more than 25) */
from   Candidate CAN
where CAN.Type = 11