Getting the error like incorrect syntax near *

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, --missing comma
    count(*) as [count(*)] -- add a count and name it 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 ^_^