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

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

Answer 2

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

Answer 3

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

Answer 4

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!]