Column "not contained in either an aggregate function or a GROUP BY clause"-CodePudding

The query:

SELECT 
    A.mrno, A.remarks, 
    B.itemcode, B.description, B.uom, B.quantity, 
    C.whsecode, MAX(C.quantity) AS whseqty, D.rate 
FROM 
    Mrhdr A
INNER JOIN 
    Mrdtls B ON A.mrno = B.mrno
INNER JOIN 
    inventoryTable C ON B.itemcode = C.itemcode
INNER JOIN 
    Items D ON B.itemcode = D.itemcode
WHERE 
    (A.mrno = @MRNo AND B.quantity < C.quantity);

The error:

Column 'Mrhdr.mrno' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

It says that the column mrno is not contained in the aggregate function of something, but when I do something about it like put it in a GROUP BY clause, the next column requests return the same error until the last column except the C.quantity column, and when they are all in a GROUP BY clause it will only return the same output not returning the highest or maximum value for the quantity. What should I do with the other columns when I use MAX or aggregate functions.

The output of the query above:

If I put all of the columns in a GROUP BY clause it returns an output with two of the itemcode FG 4751, it just removes the error of aggregate function, but I just want the highest value to be returned (just the 100, the highest quantity in the warehouse/inventory).

CodePudding user response：

The alternative to putting everything in a group by clause would be to use a window function. The question then becomes what is the MAX value relative to?

For example, you could get the MAX value based on all criteria, which would return a similar result to group by without leaving only distinct values for the column.

SELECT 
    A.mrno, 
    A.remarks, 
    B.itemcode, 
    B.description, 
    B.uom, 
    B.quantity, 
    C.whsecode, 
    MAX(C.quantity) OVER(PARTITION BY A.mrno, A.remarks, B.itemcode, B.description, B.uom, B.quantity, C.whsecode, D.rate) AS whseqty, 
    D.rate 
FROM 
    Mrhdr A
INNER JOIN 
    Mrdtls B ON A.mrno = B.mrno
INNER JOIN 
    inventoryTable C ON B.itemcode = C.itemcode
INNER JOIN 
    Items D ON B.itemcode = D.itemcode
WHERE 
    (A.mrno = @MRNo AND B.quantity < C.quantity);

CodePudding user response：

You want to dea with the maximum inventory quantity per product. But you are joining all inventory rows, where you should only pick the maximum quantity.

This can be done with a lateral join, if your DBMS supports this (you have forgtton to tell us which you are using) or simply by joining the aggregated result as follows.

SELECT 
    A.mrno, A.remarks, 
    B.itemcode, B.description, B.uom, B.quantity, 
    C.whsecode, C.whseqty, D.rate 
FROM 
    Mrhdr A
INNER JOIN 
    Mrdtls B ON A.mrno = B.mrno
INNER JOIN 
(
    SELECT itemcode, MAX(quantity) AS whseqty
    FROM inventoryTable
    GROUP BY itemcode
) C ON B.itemcode = C.itemcode
INNER JOIN 
    Items D ON B.itemcode = D.itemcode
WHERE 
    A.mrno = @MRNo AND B.quantity < C.whseqty;