CODEWITHSUNDEEP
sqlsql-server
Vap0r
Vap0r

Reputation: 2616

Not contained in GROUP BY or Aggregate function SQL Error

That's the error that I'm getting for this SQL code. I don't know the best way to go about fixing it. 

SELECT
    muo.VehicleReferenceCode as REF#,
    CONVERT(CHAR(10), muo.ActualDeliveryDate, 101) as 'Date In',
    vd.Model as Model,
    muo.DealerCategoryCode as 'Cat.',
    v.PurchaseSourceVendorCode as Vendor,
    muo.VendorCost + muo.FactoryOptionsCost as 'VEH Cost',
    muo.FreightCostAmt as Freight,
    muo.TransferredPDIPartsCost + muo.TransferredPDILaborCost as 'Trans Cost',
    SUM(woeid.h_ListPrice) - SUM(woeid.DiscountAmt) as 'Int P&A Charge',
    SUM(woeld.RegularLaborAmt) - SUM(woeld.DiscountAmt) as 'Int Labor Charge',
    muo.VendorCost 
      + muo.FactoryOptionsCost 
      + muo.FreightCostAmt 
      + muo.TransferredPDIPartsCost 
      + muo.TransferredPDILaborCost 
      + SUM(woeid.h_ListPrice) 
      - SUM(woeid.DiscountAmt) 
      + SUM(woeld.RegularLaborAmt) 
      - SUM(woeld.DiscountAmt)                 as 'VEH Total'
FROM MajorUnitOrder muo 
     INNER JOIN Vehicle v
        ON muo.VehicleIdentificationNum = v.VehicleIdentificationNum 
     JOIN VehicleDesignator vd
       ON v.VehicleDesignatorCode = vd.VehicleDesignatorCode 
     JOIN WorkOrder wo
       ON v.VehicleIdentificationNum = wo.VehicleIdentificationNum 
     JOIN WorkOrderEventItemDetail woeid
       ON wo.WorkOrderCode = woeid.WorkOrderCode 
     JOIN WorkOrderEventLaborDetail woeld
       ON woeid.WorkOrderCode = woeld.WorkOrderCode

Upvotes: 1

Views: 1648

Answers (7)

Jagadeesh G
Jagadeesh G

Reputation: 270

If you apply aggregate functions on some columns of the select columns list then you should use group by clause on columns which are not applied by aggregate functions

Example
Right Query :--
Select dept_id,sum(sal) from employee group by dept_id 

Wrong Query :-
Select dept_id,sum(sal) from employee

Upvotes: 0

veljasije
veljasije

Reputation: 7092

One solution can be that instead column, you can use subquery to fetch value from table based on current key in JOIN, in that case you can avoid putting that columns in GROUP BY.

Upvotes: 0

Curt
Curt

Reputation: 5722

Here's another way you could approach the query:

SELECT
    muo.VehicleReferenceCode                     AS REF#,
    CONVERT(CHAR(10), muo.ActualDeliveryDate, 101) AS 'Date In',
    vd.Model                                     AS Model,
    muo.DealerCategoryCode                       AS 'Cat.',
    v.PurchaseSourceVendorCode                   AS Vendor,
    muo.VendorCost + muo.FactoryOptionsCost      AS 'VEH Cost',
    muo.FreightCostAmt                           AS Freight,
    muo.TransferredPDIPartsCost 
      + muo.TransferredPDILaborCost              AS 'Trans Cost',
    COALESCE(ORDR.ListPriceSum, 0.0) 
        - COALESCE(ORDR.DiscountAmtSum, 0.0)     AS 'Int P&A Charge',
    COALESCE(LABOR.LaborAmtSum, 0.0)
        - COALESCE(LABOR.LaborDiscountSum, 0.0)  AS 'Int Labor Charge',
    muo.VendorCost 
      + muo.FactoryOptionsCost 
      + muo.FreightCostAmt 
      + muo.TransferredPDIPartsCost 
      + muo.TransferredPDILaborCost 
      + COALESCE(ORDR.ListPriceSum, 0.0)
      - COALESCE(ORDR.DiscountAmtSum, 0.0)
      + COALESCE(LABOR.LaborAmtSum, 0.0)
      - COALESCE(LABOR.LaborDiscountSum, 0.0)    AS 'VEH Total'
FROM MajorUnitOrder muo 
     INNER JOIN Vehicle v
        ON muo.VehicleIdentificationNum = v.VehicleIdentificationNum 
     JOIN VehicleDesignator vd
       ON v.VehicleDesignatorCode = vd.VehicleDesignatorCode 
     JOIN WorkOrder wo
       ON v.VehicleIdentificationNum = wo.VehicleIdentificationNum 
     LEFT JOIN 
     (
         SELECT WorkOrderCode
                SUM(h_ListPrice    AS ListPriceSum,
                SUM(DiscountAmt)   AS DiscountAmtSum,
           FROM WorkOrderEventItemDetail
                GROUP BY WorkOrderCode
     ) ORDR
       ON ORDR.WorkOrderCode = wo.WorkOrderCode
     LEFT JOIN
     (
         SELECT WorkOrderCode,
                SUM(RegularLaborAmt) AS LaborAmtSum,
                SUM(DiscountAmt)     AS LaborDiscountSum
           FROM WorkOrderEventLaborDetail
                GROUP BY WorkOrderCode
     ) LABOR
       ON LABOR.WorkOrderCode = wo.WorkOrderCode

Upvotes: 1

Chains
Chains

Reputation: 13157

SELECT
    muo.VehicleReferenceCode as REF#,
    CONVERT(CHAR(10), muo.ActualDeliveryDate, 101) as 'Date In',
    vd.Model as Model,
    muo.DealerCategoryCode as 'Cat.',
    v.PurchaseSourceVendorCode as Vendor,
    muo.VendorCost + muo.FactoryOptionsCost as 'VEH Cost',
    muo.FreightCostAmt as Freight,
    muo.TransferredPDIPartsCost + muo.TransferredPDILaborCost as 'Trans Cost',
    SUM(woeid.h_ListPrice) - SUM(woeid.DiscountAmt) as 'Int P&A Charge',
    SUM(woeld.RegularLaborAmt) - SUM(woeld.DiscountAmt) as 'Int Labor Charge',
    muo.VendorCost + muo.FactoryOptionsCost + muo.FreightCostAmt + muo.TransferredPDIPartsCost + muo.TransferredPDILaborCost + SUM(woeid.h_ListPrice) - SUM(woeid.DiscountAmt) + SUM(woeld.RegularLaborAmt) - SUM(woeld.DiscountAmt) as 'VEH Total'
FROM MajorUnitOrder muo INNER JOIN Vehicle v
    ON muo.VehicleIdentificationNum = v.VehicleIdentificationNum JOIN VehicleDesignator vd
    ON v.VehicleDesignatorCode = vd.VehicleDesignatorCode JOIN WorkOrder wo
    ON v.VehicleIdentificationNum = wo.VehicleIdentificationNum JOIN WorkOrderEventItemDetail woeid
    ON wo.WorkOrderCode = woeid.WorkOrderCode JOIN WorkOrderEventLaborDetail woeld
    ON woeid.WorkOrderCode = woeld.WorkOrderCode
GROUP BY
    muo.VehicleReferenceCode,
    CONVERT(CHAR(10), muo.ActualDeliveryDate, 101),
    vd.Model,
    muo.DealerCategoryCode,
    v.PurchaseSourceVendorCode,
    muo.VendorCost + muo.FactoryOptionsCost,
    muo.FreightCostAmt,
    muo.TransferredPDIPartsCost + muo.TransferredPDILaborCost

Upvotes: 0

Jon Raynor
Jon Raynor

Reputation: 3892

Since the aggregate SUM is used in the query, you will need to add a GROUP BY clause to tell the SQL engine how the results should be summed. For example by Model, Dealer, etc. Essentially it is missing the GROUP BY clause.

Upvotes: 0

Sir Pakington Esq
Sir Pakington Esq

Reputation: 583

If you're going to include aggregate functions in your select statement, then you need to make sure all the non-aggregate functions are included in a GROUP BY statement.

In this case, you'd need to add a GROUP BY clause that includes every single line except for 'Int P&A Charge' and 'Int Labor Charge', as they use the aggregate SUM() function.

Upvotes: 1

Curt
Curt

Reputation: 5722

You can only use the aggregate function SUM with other fields when you have a GROUP BY clause in your query

Upvotes: 6

Related Questions