Here are a couple of ideas:

This uses cross apply to get the data in a single query - but the 'distinct' is a bit clunky.

select distinct

P.productlineid,

OOS.NumberofProductsOutofStock

from

@Products P

cross apply (select count(*) from @Products P1 where P1.productlineid=P.productlineid) NOP (NumberofProducts)

cross apply (select count(*) from @Products P2 where P2.productlineid=P.productlineid

and P2.Status = 2) OOS (NumberofProductsOutofStock)

where NOP.NumberofProducts = OOS.NumberofProductsOutofStock;

I separated out the 'distinct' into a subquery as shown below:

select

P.productlineid,

OOS.NumberofProductsOutofStock

from

(

select distinct

productlineid

from

@Products

) P

cross apply (select count(*) from @Products P1 where P1.productlineid=P.productlineid) NOP (NumberofProducts)

cross apply (select count(*) from @Products P2 where P2.productlineid=P.productlineid

and P2.Status = 2) OOS (NumberofProductsOutofStock)

where NOP.NumberofProducts = OOS.NumberofProductsOutofStock;

According to the actual execution plans, both of these are a bit more efficient than the original, at the ratio of about 40% of the batch compared to 60% for the original.