selected non-aggregate values must be part of the associated group

Database
Fan

selected non-aggregate values must be part of the associated group

I'm trying to run the following query: 

SELECT 
vtiger_products.productcategory AS "Product Category",
SUM(vtiger_inventoryproductrel.quantity) AS "Line Item Qt"
FROM bi_batch.vtiger_inventoryproductrel
INNER JOIN bi_batch.vtiger_products
ON vtiger_products.productid = vtiger_inventoryproductrel.productid

getting error - SELECT Failed. 3504: selected non-aggregate values must be part of the associated group

Can someone help?

6 REPLIES
Senior Apprentice

Re: selected non-aggregate values must be part of the associated group

Ahh, the well known 3504 error :-)

You have to add GROUP BY 1 or GROUP BY vtiger_products.productcategory

Dieter

Fan

Re: selected non-aggregate values must be part of the associated group

Does that means I have to put all the select columns in Group By?

I'm converting a mysql to teradata sql, in that query Select is calling 20 columns but Group by just have 2 columns (1 of which is not called in select). I though SQL basics are same in both.

Senior Apprentice

Re: selected non-aggregate values must be part of the associated group

Yo're right, whenever there's an aggregation all other columns must be in GROUP BY.

MySQL considers some bad practice as valid SQL, but all other DBMSes consider that illegal :-)

What does MySQL return? A random productcategory? Or the same as GROUP BY 1? 

Dieter

Fan

Re: selected non-aggregate values must be part of the associated group

Gotcha! the result is same :) Thanks a lot.

I'm selecting 20 columns in query is there any shortcut for GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 ???

thats too much for bigger queries.

Senior Apprentice

Re: selected non-aggregate values must be part of the associated group

GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 is aleady a shortcut :-)

Most DBMSes require to explicitly list all column names instead of column numbers.

Dieter

Fan

Re: selected non-aggregate values must be part of the associated group

Thanks :)