SQL Count in View as column

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开发者_如何学JAVA
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

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

Try this:

SELECT  
    ProductID,  
    Name,  
    Description,  
    Price,  
count(*) as totalnumberoforders
FROM 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 TotalNumberOfOrders
FROM tblProducts prod
INNER JOIN tblOrders ord ON prod.ProductID = ord.ProductID

This will work:

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

The "COUNT(ord.ProductId)" clause ensures that if no orders are found, TotalNumberOfOrders will be equal to zero.

[Later edit: I forgot the GROUP BY clause. Doh!]