Bh.fold as Count(*) can't work because of the use of the keyword.
You can turn this into an implicit column header by either adding square braces or single quotes:
select Th.groupY,
Th.CDPY,
Bh.fold as [count(*)]
from dbo.tblTraceHeaders as Th
left outer join dbo.tblBinaryHeaders as Bh on Th.fileID=Bh.fileID where Th.fileID in (1,5,3)
Group By Th.groupY,Th.CDPY,Bh.fold
having
count(*)> 1 and Th.fileID in (1,5,3)
Is this not what you intended?
maybe you want to use the count(*) instead of naming the column such?
select Th.groupY,
Th.CDPY,
Bh.fold,
count(*) as [count(*)]
from dbo.tblTraceHeaders as Th
left outer join dbo.tblBinaryHeaders as Bh on Th.fileID=Bh.fileID where Th.fileID in (1,5,3)
Group By Th.groupY,Th.CDPY,Bh.fold
having
count(*)> 1 and Th.fileID in (1,5,3)
let me know if neither of these are what you need ^_^