Search code examples
sqlgroup-byderby

GROUP BY SQL statement Error

I got this statement 

SELECT ITEM.ITEMID, ITEMNAME, QUANTITY AS "QUANTITY SOLD" 
FROM ORDERITEM,
     NBUSER."ORDER",
     PAYMENT,
     ITEM
WHERE NBUSER."ORDER".PAYMENTID = PAYMENT.PAYMENTID
  AND ITEM.ITEMID = ORDERITEM.ITEMID
  AND PAYMENT.PAYMENTDATE BETWEEN '4/1/2017' AND '4/30/2017' 
GROUP BY ITEM.ITEMID
ORDER BY ITEM.ITEMID DESC;

But it keeps giving me this error:

[Exception, Error code 30,000, SQLState 42Y36] Column reference 'ITEM.ITEMNAME' is invalid, or is part of an invalid expression. For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions. Line 1, column 1

I want to join the records with similar itemid together and adding the quantity up for all the same itemid.

Solution

    1. First of all, you have 4 tables in the statement, but only 2 joins. Do you do this intentionally? If not, you need to specify 1 more join.

    2. When you use group by only for a part of columns in select, you must have an aggregate function for remaining columns in the select clause, in your case, for example:

    select ITEM.ITEMID, max(ITEMNAME), sum(QUANTITY) AS "QUANTITY SOLD" ...
    1. It is better to use table aliases for each column, otherwise, it is difficult to understand the sql statement in general. You have defined alias here - ITEM.ITEMID (ITEM is an alias), but not there - ITEMNAME.