我不知道你用的是哪种db（所以我也无法测试我的答案）。
但一般来说 IN 您需要子查询只返回一列。
所以查询应该被删除 Sum(Total-allocated) as Bal ):

select
  CC.ContactID, 

from 
  Client C 
  join Contacts CC on CC.ContactID = C.ContactID 
 where 

        ClientID In (

                                  Select 
                                    ClientID 
                                  from 
                                    Main 
                                  Where 
                                    Total <> Allocated
                                    and NomTypeID < 10 
                                  Group By 
                                    ClientID
                                  HAVING Sum(Total-allocated) > 10
                             )

请使用下面的查询，

select
  CC.ContactID from  
Client C 
join Contacts CC on CC.ContactID = C.ContactID 
join (Select ClientID,Sum(Total-allocated) as Bal from Main Where 
Total <> Allocated and NomTypeID < 10 
Group By ClientID HAVING Sum(Total-allocated) > 10)) m
on (C.ClientID = m.ClientID);