The ccount is not obtain directly from table as done here.Am rather using the View written as below

With compTable(cid,ccid,ccount)

as

AS

(

select parentid,childrenId,COUNT(cmpcd) FROM

group by parentid,childrenId,cmpcd

)

select decsr,

pnumber,

(SELECT count(ccount) FROM compTable where parentid=ch.cn_id) as [Count] ,--[this is ccount]

-- here i need the total count

from table ch inner join table b

on ch.id=b.id