I'm trying to run the following query:
vtiger_products.productcategory AS "Product Category",
SUM(vtiger_inventoryproductrel.quantity) AS "Line Item Qt"
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?
Ahh, the well known 3504 error :-)
You have to add GROUP BY 1 or GROUP BY vtiger_products.productcategory
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.
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?
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.
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.