select Productname,
	--(SELECT COUNT(1) FROM Product.Sales s WHERE s.ProductKey = Product.Productkey AND QuantityOrdered > 10000 ) AS MoreThan10000,
	SUM(CASE WHEN QuantityOrdered > 10000 THEN 1 ELSE 0 END) AS MoreThan10000,
	--(SELECT COUNT(1) FROM Product.Sales s WHERE s.ProductKey = Product.Productkey AND  QuantityOrdered <= 10000 AND QuantityOrdered > 5000) AS Between5001And10000,
	SUM(CASE WHEN QuantityOrdered <= 10000 AND QuantityOrdered > 5000 THEN 1 ELSE 0 END) AS Between5001And10000,
	--(SELECT COUNT(1) FROM Product.Sales s WHERE s.ProductKey = Product.Productkey AND QuatityOrdered <=5000 AND QuantityOrdered > 0 ) AS Between1And5000,
	SUM(CASE WHEN QuatityOrdered <=5000 AND QuantityOrdered > 0 THEN 1 ELSE 0 END) AS Between1And5000,
	--(SELECT COUNT(1) FROM Product.Product p left join Product.Sales s On s.ProductKey = p.Productkey where p.ProductKey = Product.Productkey AND s.SalesKey IS NULL) AS NeverOrdered,
	SUM(CASE WHEN Sales.SalesKey IS NULL THEN 1 ELSE 0 END) AS NeverOrdered
from Product.Product
left join Product.Sales On Sales.ProductKey = Product.Productkey
where .....

DECLARE @Table sysname = 'TableWith250millRows'
SELECT
    c.row_count AS [RowCount]
FROM
    sys.objects a
JOIN 
    sys.indexes b
    ON b.OBJECT_ID = a.OBJECT_ID
JOIN 
    sys.dm_db_partition_stats AS c
    ON b.OBJECT_ID = c.OBJECT_ID
       AND b.index_id = c.index_id
WHERE
    b.index_id < 2
    AND a.is_ms_shipped = 0
    AND a.name = @Table