What is the point of such strange code? Why not take the count into a variable first and then add it as a field whatever number you need?

In addition, the problem you're facing is called parameter sniffing. Check Plamen Ratchev article

Parameter Sniffing

>I've written a query with several nested joins to check status of records being transferred successfully to archive tables. It looks something like this:
>

>SELECT maintable.pk
>		,a.rcount
>		,b.rcount
>		,c.rcount
>		,d.rcount
>		,e.rcount
>		,f.rcount
>	from maintable
>		left join (select COUNT(*) as rcount from childtable1 where pk=12345) as a on 1=1
>		left join (select COUNT(*) as rcount from childtable2 where pk=12345) as b on 1=1
>		left join (select COUNT(*) as rcount from childtable3 where pk=12345) as c on 1=1
>		left join (select COUNT(*) as rcount from childtable4 where pk=12345) as d on 1=1
>		left join (select COUNT(*) as rcount from childtable5 where pk=12345) as e on 1=1
>		left join (select COUNT(*) as rcount from childtable6 where pk=12345) as f on 1=1
>	where maintable.pk=12345
>

>this works fine and runs instantly.
>
>If I change it to a variable like:
>
>

>DECLARE @lnPK as int=12345
>SELECT maintable.pk
>		,a.rcount
>		,b.rcount
>		,c.rcount
>		,d.rcount
>		,e.rcount
>		,f.rcount
>	from maintable
>		left join (select COUNT(*) as rcount from childtable1 where pk=@lnPK) as a on 1=1
>		left join (select COUNT(*) as rcount from childtable2 where pk=@lnPK) as b on 1=1
>		left join (select COUNT(*) as rcount from childtable3 where pk=@lnPK) as c on 1=1
>		left join (select COUNT(*) as rcount from childtable4 where pk=@lnPK) as d on 1=1
>		left join (select COUNT(*) as rcount from childtable5 where pk=@lnPK) as e on 1=1
>		left join (select COUNT(*) as rcount from childtable6 where pk=@lnPK) as f on 1=1
>	where maintable.pk=@lnPK
>

>Performance goes to hell. It takes 45 seconds to complete and the execution plan is completely different.
>
>What's going on here? Is there a better way to do this with a variable?