SELECT pc.Name
, ppsc.Name
, ( SELECT COUNT(*) FROM Production.Product pp
WHERE pp.ProductSubcategoryID = ppsc.ProductSubcategoryID) as NumberOfProducts
FROM Production.ProductCategory pc
INNER JOIN Production.ProductSubCategory ppsc
ON pc.ProductcategoryID = ppsc.ProductcategoryID
AND
SELECT pw.ProductID
, pp.Name
, ppsc.Name
, SUM(pw.OrderQty) as Quantity
FROM Production.Product pp
LEFT JOIN Production.ProductSubCategory ppsc
ON pp.ProductSubcategoryID = ppsc.ProductSubcategoryID
INNER JOIN Production.WorkOrder pw
ON pw.ProductID = pp.ProductID
WHERE pw.DueDate = '2003-06-03 00:00:00.000'
GROUP BY pw.ProductID, pp.Name, ppsc.Name
Hi,
Thks about your rapid reply. I would like to know if i can do that query without the subselect and other way to do the sum () function on second case.