select t1.fclass1,
       t1.fclass2,
       t1.ftitle,
       t1.submitby,
       t1.submitdate,
       t1.checkdate,
       t1.checkby,
       t1.createby,
       t1.createdate,
       t1.fnotes,
       t1.tarchives_adid,
       t1.fstatus,
       isnull(t1.fvisitors, 0) + 1  as                           fvisitors,
       t1.fshares,
       case when t2.tarchives_ad_colid IS null then 0 else 1 end fiscollection,
       count(t3.tarchives_ad_shaid) as                           fsharecustcount
from tarchives_ad t1
         left join tarchives_ad_collection t2
                   on t1.siteid = t2.siteid and t1.tarchives_adid = t2.tarchives_adid and t2.tenterprise_userid =$tenterprise_userid$
    left join tarchives_ad_shareform t3
on t1.siteid=t3.siteid and t1.tarchives_adid=t3.tarchives_adid
where t1.siteid =$siteid$ and t1.tarchives_adid=$tarchives_adid$
group by t1.fclass1,
    t1.fclass2,
    t1.ftitle,
    t1.submitby,
    t1.submitdate,
    t1.checkdate,
    t1.checkby,
    t1.createby,
    t1.createdate,
    t1.fnotes,
    t1.tarchives_adid,
    t1.fstatus,
    isnull(t1.fvisitors, 0) + 1,
    t1.fshares,
    case when t2.tarchives_ad_colid IS null then 0
else
1 end