Parent Child count- so complicated!

The sample data generation scripts are having syntax errors,

Try below,

create table #Something
(
Line_no int,
code varchar(5),
AccountNo char(5)
)

insert into #Something 
select 12311, 'P1c', 'Ac115' union all
select 12311, 'L1', 'Ac115' union all
select 123, 'C1', 'Ac115' union all
select 1222, 'C1', 'Ac115' union all
select 1243, 'C1', 'Ac115' union all
select 433, 'P1a', 'Ac111' union all
select 433, 'L1', 'Ac111' union all
select 4331, 'C1', 'Ac111' union all
select 543, 'C1', 'Ac222' union all
select 544, 'C1', 'Ac222' union all
select 4322, 'P1b', 'Ac222' union all
select 4322, 'L1', 'Ac222' union all

select 8766, 'P1d' , 'Ab111' union all
select 8766 ,'L1' , 'Ab111' union all
select 8767 ,'C1', 'Ab111' union all
select 8789 ,'P1d', 'Ab119' union all
select 8766 ,'L1', 'Ab119' union all
select 876654, 'C1', 'Ab119' union all
select 876655, 'C1', 'Ab119' union all
select 876698, 'P1a', 'Ab117' union all
select 876698, 'L1', 'Ab117' union all
select 987 ,'C1', 'Ab117' union all
select 555444, 'P1d' ,'Xcv' union all
select 555444 ,'L1', 'Xcv' union all
select 6754 ,'C1', 'Xcv' 


;with cte 
as (
select distinct code,AccountNo ,
(select COUNT(*) from #Something T2 where T1.AccountNo=T2.AccountNo and T2.code like 'c%') [Child line Count]
 from #Something T1 where code like 'p%'
)
select code ,COUNT(*) [Parent line Count] ,SUM([Child line Count])
from cte
group by code

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.