CODEWITHSUNDEEP
sqlgroup-bymaxaggregate-functions
Richard Johnson
Richard Johnson

Reputation: 393

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

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:

enter image description here

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).

Upvotes: 0

Views: 1289

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

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

This can be done with a lateral join, if your DBMS supports this (you have forgotton to tell us which you are using) or simply by joining the rows in question by applying a window function 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, whsecode, quantity as whseqty,
      MAX(quantity) OVER (PARTITION BY itemcode) AS max_qty
    FROM inventoryTable
) C ON B.itemcode = C.itemcode AND C.whseqty = C.max_qty
INNER JOIN 
    Items D ON B.itemcode = D.itemcode
WHERE 
    A.mrno = @MRNo AND B.quantity < C.whseqty;

This query should work in most DBMS. If you are working with a DBMS that supports the standard SQL FETCH WITH TIES clause, I'd change the join to:

INNER JOIN 
(
    SELECT itemcode, whsecode, quantity as whseqty
    FROM inventoryTable
    ORDER BY RANK() OVER (PARTITION BY itemcode ORDER BY quantity DESC)
    FETCH FIRST ROW WITH TIES
) C ON B.itemcode = C.itemcode

so as to only select the top rows inside the subquery already and not to awkwardly filter them later. But well, a lateral join may even be considered more straight-forward here.

Upvotes: 1

procopypaster
procopypaster

Reputation: 416

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);

Upvotes: 0

Related Questions