I'm trying to get the result of a COUNT as a column in my view. Please see the below query for a demo of the kind of thing I want (this is just for demo purposes)

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

FROM tblProducts prod
LEFT JOIN tblOrders ord ON prod.ProductID = ord.ProductID

This obviously isn't working... but I was wondering what the correct way of doing this would be?