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]