none
Cannot perform an aggregate function on an expression containing an aggregate or a subquery

    Question

  • How do i get around this???

    select

     

    sum(PODet.UnitCost*ReceiverDet.Qty2Receive) as TotalCost

     

    ,sum(ReceiverDet.Qty2Receive) as TotalQty, SUM(TotalCost/TotalQty)

     

    FROM ReceiverDet

     

    INNER JOIN PODet ON ReceiverDet.PartNo = PODet.PartNo AND ReceiverDet.PONum = PODet.PONum

     

    INNER JOIN Receiver ON ReceiverDet.ReceiverNo = Receiver.ReceiverNo

     

    WHERE ReceiverDet.PartNo = 'hcr3508' and Receiver.ReceiveDate > '2010-08-23 06:18:03.000'

    i want to be able to SUM(TotalCost/TotalQty)??????? and if there is no value in that date range go back another 3 months up to 3yrs????

    Thursday, September 23, 2010 3:33 PM

Answers

  • select SUM(TotalCost/TotalQty)
    from
    (
    select
    sum(PODet.UnitCost*ReceiverDet.Qty2Receive) as TotalCost 
    
    ,sum(ReceiverDet.Qty2Receive) as TotalQty
     FROM ReceiverDet 
    
    INNER JOIN PODet ON ReceiverDet.PartNo = PODet.PartNo AND ReceiverDet.PONum = PODet.PONum 
    
     INNER JOIN Receiver ON ReceiverDet.ReceiverNo = Receiver.ReceiverNo 
    
    WHERE ReceiverDet.PartNo = 'hcr3508' and Receiver.ReceiveDate > '2010-08-23 06:18:03.000'
    ) t
    
    

    http://www.t-sql.ru
    • Marked as answer by Ai-hua Qiu Friday, October 1, 2010 9:30 AM
    Thursday, September 23, 2010 3:36 PM