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;

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;

declare @ProductLine table
(ProductlineID int)
declare @Products table
(ProductLineID int,
 ProductID int,
 Status int)
 insert into @ProductLine values (1)
 Insert into @ProductLine values (2)
 Insert into @ProductLine values (3)
 Insert into @ProductLine values (4)
 
Insert into @Products values (1,100, 1)
 insert into @Products values (1,200, 2)
 Insert into @Products values (1,300, 1)
 insert into @Products values (1,400, 2)
 Insert into @Products values (2,1000, 1)
 insert into @Products values (2,2000, 1)
 Insert into @Products values (2,3000, 1)
 insert into @Products values (3,10, 2)
 insert into @Products values (4,1, 2)
 insert into @Products values (4,2, 2)
 -- find all product lines with all products out of stock. A product is out of stock has status=2
 -- From input above, product lines 3 and 4 will be reported.
SELECT
a.ProductLineID,
Productsoutofstock = SUM(CASE b.Status WHEN 2 THEN 1 ELSE 0 END)
FROM 
@ProductLine a JOIN @Products b ON 
a.ProductlineID = b.ProductLineID
WHERE
NOT EXISTS (SELECT 1 FROM @Products c WHERE a.ProductlineID = c.ProductLineID AND c.status != 2) 
GROUP BY 
a.ProductlIneid 

SELECT productlineid, NumberofProductsOutofStock=COUNT(productlineid)
FROM @Products
GROUP BY productlineid
HAVING COUNT(CASE Status WHEN 2 THEN 1 ELSE NULL END) = COUNT(productlineid)