SQL Count in View as column

Question

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?

I am using SQL Server

Answer 1

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 TotalNumberOfOrders
FROM 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 TotalNumberOfOrders
FROM tblProducts prod
LEFT JOIN tblOrders ord ON prod.ProductID = ord.ProductID
GROUP BY
    ProductID, 
    Name, 
    Description, 
    Price