sql query error-invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

SELECT  i.DocumentNo,i.DocumentDate,i.Qty, pa.ProductCode,
        MAX(pmx.ProductName),pa.ProductAssembleCode, 
        MAX(pm.ProductName) ProductAsemblyName,
        MAX(pa.ProductAssembleUnit),pa.ProductAssembleQty,
        MAX(pa.ProductAssembleUnitCost), pa.ProductAssembleCostValue
FROM    dbo.InvSales i
INNER JOIN dbo.InvProductAssemble pa ON i.ProductCode = pa.ProductCode
INNER JOIN dbo.InvProductMaster pm ON pa.ProductAssembleID = pm.InvProductMasterID
INNER JOIN dbo.InvProductMaster pmx ON i.ProductID = pmx.InvProductMasterID
WHERE DocumentDate BETWEEN '2016-06-10' AND '2017-12-14'
GROUP BY i.ProductCode,ProductAssembleCode

this is my Query for food coasting report .i cant find what is the error of this query error- Column 'dbo.InvSales.DocumentNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) — Piro, Dec 14 '17 at 06:52

Paul Maxwell · Accepted Answer · 2017-12-14T06:38:30.293

That error message tells you what the problem is:

SELECT
      i.DocumentNo -- dbo.InvSales.DocumentNo' is invalid in the select list
    , i.DocumentDate
    , i.Qty
    , pa.ProductCode
    , MAX(pmx.ProductName)
    , pa.ProductAssembleCode
    , MAX(pm.ProductName) productasemblyname
    , MAX(pa.ProductAssembleUnit)
    , pa.ProductAssembleQty
    , MAX(pa.ProductAssembleUnitCost)
    , pa.ProductAssembleCostValue
FROM dbo.InvSales i
INNER JOIN dbo.InvProductAssemble pa ON i.ProductCode = pa.ProductCode
INNER JOIN dbo.InvProductMaster pm ON pa.ProductAssembleID = pm.InvProductMasterID
INNER JOIN dbo.InvProductMaster pmx ON i.ProductID = pmx.InvProductMasterID
WHERE DocumentDate BETWEEN '2016-06-10' AND '2017-12-14'
GROUP BY
      i.DocumentNo -- because is WAS NOT HERE as well
    , i.DocumentDate
    , i.Qty
    , pa.ProductCode
    , pa.ProductAssembleCode
    , pa.ProductAssembleQty
    , pa.ProductAssembleCostValue

Any column in the select list, that is NOT using an aggregate function, should be listed in the group by clause. If you don't do that the error message will occur and it is trying to say:

put the column into the group by clause, OR
you may have forgotten to use an aggregate function on that column

It's most commonly problem 1

sql query error-invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

1 Answers1