Search code examples
javajpaderbyjpql

42Y36: SELECT list may only contain grouping columns, grouping 'table.*' not allowed

The following query works like a beauty. Now I'm trying to select PRODUCT.* which leads to errorcode

42Y36: (Column reference '<value>' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.)

Next I tried to set both the SELECT and GROUP BY to PRODUCT.* which leads to errorcode

42X01: (Syntax error: <error>.)

A plain syntax error. Ok, seems it's not allowed to use wildcards in a GROUP BY. So my question is (without the need of a subquery), "How can I select the whole object while using the defined HAVING-clause?"

SELECT      PRODUCT.ID
FROM        PRODUCT
LEFT JOIN   DELIVERYROW   ON  DELIVERYROW.PRODUCT_ID = PRODUCT.ID
LEFT JOIN   DISPATCHROW   ON  DISPATCHROW.PRODUCT_ID = PRODUCT.ID
WHERE       PRODUCT.DTYPE = 'PRODUCT'
GROUP BY    PRODUCT.ID
HAVING      (
                COALESCE(SUM(DELIVERYROW.AMOUNT),0) 
                - 
                COALESCE(SUM(DISPATCHROW.AMOUNT),0)
            ) > 0
Solution

  • You can't have items in the select that are not in the group by unless you are going to use a summary function on them.

    You can include other values in the select but you either have to group by them OR perform SUM(), AVG(), MIN(), MAX() etc... on them, this is why product.* wont work in the select, you are saying giving me all columns but only group by product.id

    expanding answer to explain group by...

    Lets pretend the product table has four cols | id | cost | name | date | If you group by id you are collapsing all rows with the same id into one row, if you want other columns to be present you must perform an aggregate function on them, for example we could group by id and get min(date), and max(date) to get the min/max date for each product id, we could also sum(cost) and avg(cost) to get summary and average.

    You can group by more than one column, so we could group by id and name, getting a row for each unique combination of the two. You can not group by *, that wouldn't make much sense since it will simply return your entire table.

    The error you are getting is due to you trying to select a column that is not in your group by.

    What do you want the output to show?