Count(*) in a subquery

Question

I need the number of line items per Order.

In this example, order 10 has 0 and order 11 has 2 lines.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OrderDetail') AND type in (N'U'))
DROP TABLE OrderDetail
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OrderHeader') AND type in (N'U'))
DROP Table OrderHeader
GO
CREATE TABLE OrderHeader(
OrderHeaderID Int Primary Key Identity(10,1),
CustID Int)
GO
CREATE TABLE OrderDetail(
OrderDetailID Int Primary Key Identity(100,1),
OrderHeaderID Int REFERENCES OrderHeader,
ItemID Int)
GO
DECLARE @OrderHeaderID Int
INSERT INTO OrderHeader(CustID) VALUES(1000)
INSERT INTO OrderHeader(CustID) VALUES(1001)
SET @OrderHeaderID=SCOPE_IDENTITY()
INSERT INTO OrderDetail(OrderHeaderID,ItemID) VALUES(@OrderHeaderID,3)
INSERT INTO OrderDetail(OrderHeaderID,ItemID) VALUES(@OrderHeaderID,4)
GO
SELECT 
OrderDetailID,ItemID,OrderHeader.OrderHeaderID,CustID
FROM OrderDetail
JOIN OrderHeader
ON OrderDetail.OrderHeaderID=OrderHeader.OrderHeaderID
--

This is where I need help I need COUNT(*)

FROM OrderDetail WHERE OrderDetail.OrderHeaderID=OrderHeader.OrderHeaderID
SELECT OrderHeader.OrderHeaderID,CustID,CountOfOrderDetail
FROM OrderHeader
JOIN OrderDetail
ON OrderDetail.OrderHeaderID=OrderHeader.OrderHeaderID

Answer 1

SELECT OrderHeader.ID, count(OrderDetail.ID)
FROM OrderHeader
LEFT JOIN OrderDetail
ON OrderDetail.OrderHeaderID=OrderHeader.OrderHeaderID
GROUP BY OrderHeader.ID

Answer 2

Like this?

SELECT 
  h.OrderHeaderID,
  h.CustID,
  COUNT(d.OrderHeaderID) CountOfOrderDetail
FROM 
  OrderHeader h
  LEFT JOIN OrderDetail d ON d.OrderHeaderID = h.OrderHeaderID
GROUP BY
  h.OrderHeaderID,
  h.CustID

Answer 3

For future reference, you'll want to avoid using the following:

SELECT COUNT(*)
FROM OrderDetail
GROUP BY OrderHeaderID

It seems simple and elegant enough at first glance, but note that you'll miss any order from the OrderHeader table that has no orders. If it is important that they be represented (with a 0 for their count), see Tomalak or Autocracy's answers here and here.

Thanks to KM for pointing this out! (see comments below)

Answer 4

select 
   ord.OrderHeaderID
   , count(det.ItemID)
from
   OrderHeader ord
left outer join
   OrderDetail det
on
   det.OrderHeaderID = ord.OrderHeaderID
group by
   ord.OrderHeaderID