Your test data is flawed because it is missing some commas in the INSERT/SELECT and you get truncation errors putting it into the data types you supplied in the CREATE TABLE (see comment below). Nonetheless, this runs:

create table #Something

(

Line_no int,

code varchar(5),

AccountNo char(5)

)

insert #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 8767, 'C1', 'Ab111' union all

select 8789, 'C1', 'Ab111' union all -- last 2 rows had leading space in last column

select 8766, 'L1', 'Ab111'

And this delivers your expected results:

SELECT [Parent code]=a.code

,[Parent Line Count]=COUNT(a.Line_no) OVER (PARTITION BY a.code)

,[Child Line Count]=COUNT(*)

FROM #Something a

JOIN #Something b ON a.Line_No = b.Line_No AND

LEFT(a.code, 1) = 'P' AND LEFT(b.code, 1) = 'L'

JOIN #Something c ON b.AccountNo = c.AccountNo AND

LEFT(c.code, 1) NOT IN ('P', 'L')

GROUP BY a.code, a.Line_no;

However, none of your test data is constructed for a case where the [Parent Line Count] may be > 1. So I must leave that up to you to test.