OrderHeader.*,COUNT(OrderDetail) from a subquery

Question

An earlier question was answered about getting the OrderID and the number of associated line items. Now I would like to get the rest of the fields in the OrderHeader row.

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,
    OrderDate DateTime default getdate(),
    OrderTypeID Int default 0, -- References OrderType
    OrderGroupID Int Default 0, -- References OrderGroup
    OrderCatID Int Default 0 -- References OrderCat
    )
    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
    -- This query gives me the number of line items per order
    SELECT OH.OrderHeaderID,COUNT(OD.OrderHeaderID) AS CountOfOrderDetail
    FROM OrderHeader OH
    LEFT JOIN OrderDetail OD 
    ON OD.OrderHeaderID = OH.OrderHeaderID
    GROUP BY OH.OrderHeaderID
    /*
    This is where I need help
    I would like a query to show
    SELECT OrderHeader.*,CountOfOrderDetail
    */

Answer 1

SELECT
    OrderHeader.*, 
    OrderDetailCount.CountOfOrderDetail
FROM OrderHeader
JOIN (
    SELECT 
       OH.OrderHeaderId,
       COUNT(OD.OrderHeaderID) AS CountOfOrderDetail    
    FROM OrderHeader OH    
    LEFT JOIN OrderDetail OD ON 
       OD.OrderHeaderID = OH.OrderHeaderID    
    GROUP BY OH.OrderHeaderID
) as OrderDetailCount ON
    OrderHeader.OrderHeaderId = OrderDetailCount.OrderHeaderId

Answer 2

How about:

select oh.*, (
    select count(*) 
    from OrderDetail 
    where OrderHeaderID = oh.OrderHeaderID) as CountOfOrderDetail
from OrderHeader oh

Answer 3

SELECT h.OrderHeaderID,
        h.CustID,
        h.OrderDate,
        h.OrderTypeID,
        h.OrderGroupID,
        h.OrderCatID,
        COUNT(*) 
FROM OrderHeader h 
JOIN OrderDetail d ON h.OrderHeaderId = d.OrderHeaderId 
GROUP BY h.OrderHeaderID,
        h.CustID,
        h.OrderDate,
        h.OrderTypeID,
        h.OrderGroupID,
        h.OrderCatID