SQL Count in View as column

Your query would actually work if you removed the join - it's not actually used, and it will cause the ord table within the inner select subquery to conflict with the ord table that you've joined to:

SELECT     ProductID,     Name,     Description,     Price,     (SELECT COUNT(*) FROM ord WHERE ord.ProductID = prod.ProductID) AS TotalNumberOfOrdersFROM tblProducts prod

Alternatively, you can actually make use of the joined table in conjunction with Group By:

SELECT     ProductID,     Name,     Description,     Price,     COUNT(ord.ProductID) AS TotalNumberOfOrdersFROM tblProducts prodLEFT JOIN tblOrders ord ON prod.ProductID = ord.ProductIDGROUP BY    ProductID,     Name,     Description,     Price

Try this:

SELECT      ProductID,      Name,      Description,      Price,  count(*) as totalnumberofordersFROM tblProducts prod LEFT JOIN tblOrders ord ON prod.ProductID = ord.ProductID group by    ProductID,      Name,      Description,      Price

If you are only interested in the products that have been ordered, you could simply substitute the LEFT OUTER JOIN operation with an INNER JOIN:

SELECT     prod.ProductID,     prod.Name,     prod.Description,     prod. Price,     COUNT(*) AS TotalNumberOfOrdersFROM tblProducts prodINNER JOIN tblOrders ord ON prod.ProductID = ord.ProductID