SQL Count in Column View

Question

I am trying to get the result of COUNT as a column in my opinion. Please see below request for a demonstration of what 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 doesn't work ... but I was wondering how to do it right?

I am using SQL Server

Answer 1

Your query will really work if you delete the connection - it is not actually used, and it will call the ord table inside the internal select subquery to conflict with the ord table that you joined:

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

Alternatively, you can use a join table in combination 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 2

Try the following:

 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 3

If you are only interested in the products that were ordered, you can simply replace the LEFT OUTER JOIN INNER JOIN operation:

 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 guarantees that if no orders are found, then TotalNumberOfOrders will be zero.

[Late editing: I forgot the GROUP BY clause. Doh!]