Execute the following script in Query Editor demonstrate the usage of CTE GROUP BY aggregate to JOIN to detail data lines.

Use AdventureWorks2008;

WITH ctePOTotal AS

 (

        SELECT

            PurchaseOrderID,

            Sum(OrderQty) as [TotalQty]

        FROM Purchasing.PurchaseOrderDetail T2

        GROUP BY PurchaseOrderID

    )

SELECT

    PO = pod.PurchaseOrderID,

    LineItem=ROW_NUMBER()OVER (PARTITION BY pod.PurchaseOrderID ORDER BY ProductId),

    OrderDate = convert(varchar,OrderDate,110),

    ProductId,

    UnitPrice,

    OrderQty,

    TotalQty=ctePOTotal.[TotalQty]

FROM Purchasing.PurchaseOrderHeader poh

    INNER JOIN Purchasing.PurchaseOrderDetail pod

        ON poh.PurchaseOrderId = pod.PurchaseOrderId

    INNER JOIN  ctePOTotal

        ON pod.PurchaseOrderID = ctePOTotal.PurchaseOrderID

WHERE Year(OrderDate)=2004

ORDER BY PO, LineItem