0

I had the following error: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'II.quantity' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I've tried with the following code (sorry the uppercase) to group all the money spent by city, and product:

SELECT CI.CITY_NAME, P.PRODUCT_NAME, (II.QUANTITY * II.PRICE) AS AMOUNT_SPENT
FROM CITY AS CI
INNER JOIN CUSTOMER AS CU
ON CI.ID = CU.CITY_ID
INNER JOIN INVOICE AS I
ON I.CUSTOMER_ID = CU.ID
INNER JOIN INVOICE_ITEM AS II
ON II.INVOICE_ID = I.ID
INNER JOIN PRODUCT AS P
ON P.ID = II.PRODUCT_ID
GROUP BY CI.CITY_NAME, P.PRODUCT_NAME
ORDER BY AMOUNT_SPENT DESC, CI.CITY_NAME ASC, P.PRODUCT_NAME;
Angelo Bejarano Ciotti
  • 1
  • 1
  • A product is no aggregation, meaning the part (II.QUANTITY * II.PRICE) will multiply the quantity with the price, but quantity and price are still missing to be put either in an aggregation or in your group by clause. I assume you want to write SUM(II.QUANTITY * II.PRICE) instead? Adding sample input and outcome would make it clearer. – Jonas Metzler Dec 23 '22 at 07:30

0 Answers0