SELECT [Name]

, COUNT(*) AS 'RowCount'

, SUM(LineTotal) AS 'TotalValue'

FROM Production.Product P

JOIN Sales.SalesOrderDetail D ON D.ProductID = P.ProductID

GROUP BY ProductID

should be

SELECT [Name]

, COUNT(*) AS 'RowCount'

, SUM(LineTotal) AS 'TotalValue'

FROM Production.Product P

JOIN Sales.SalesOrderDetail D ON D.ProductID = P.ProductID

GROUP BY [Name]