You can translate the question and the replies:

facing error : Field 'TC_1.inventory_days' is not a group by field

Hi team, While executing the below query i am getting an error "Field 'TC_1.inventory_days' is not a group by field" .Kindly help me on this SELECT FLOOR("TC_1"."inventory_days" / (NULLIF(5,0.0)* 1.0)) "COL_1", COUNT("TC_1"."inventory_days") "COL_2" FROM "storesales" "TC_1" INNER JOIN ( SELECT 'No' as "COL_1", 1 as "NULL_COL" ) "MDF_1" ON ("TC_1"."issouthindia" = "MDF_1"."COL_1")
user
11-10-2023 05:50:46 -0400
code

3 Answers

Hi, In SQL, the COUNT is an aggregation function used to count the number of occurrences within a table. When selecting a single field, using this function will work just fine. Meanwhile, if you select other fields in this same query it will throw an error. I suggest to add a GROUP BY at the end of the query to specify the fields on which the aggregation operation will be performed. Hope this helps.
Denodo Team
11-10-2023 09:45:19 -0400
code
Hi team, I have tried by executing the below query and it works. SELECT FLOOR("TC_1"."inventory_days" / (NULLIF(5,0.0)* 1.0)) "COL_1", COUNT("TC_1"."inventory_days") "COL_2" FROM "storesales" "TC_1" group by 1 But if i am adding the group by column to the below it throws an error 'The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.' Query executed : SELECT FLOOR("TC_1"."inventory_days" / (NULLIF(5,0.0)* 1.0)) "COL_1", COUNT("TC_1"."inventory_days") "COL_2" FROM "storesales" "TC_1" INNER JOIN ( SELECT 'No' as "COL_1", 1 as "NULL_COL" ) "MDF_1" ON ("TC_1"."issouthindia" = "MDF_1"."COL_1") group by 1. Why group by works in the first query and not working in the second query?
user
12-10-2023 01:45:08 -0400
Hi, I would try to rewrite the sql query as follows : *SELECT FLOOR("TC1"."inventorydays" / (NULLIF(5,0.0) 1.0)) "COL1", COUNT("TC1"."inventorydays") "COL2" FROM "storesales" "TC1" WHERE "TC1"."issouthindia" = 'No" group by 1* The inner join in the following query *SELECT FLOOR("TC1"."inventorydays" / (NULLIF(5,0.0) 1.0)) "COL1", COUNT("TC1"."inventorydays") "COL2" FROM "storesales" "TC1" INNER JOIN ( SELECT 'No' as "COL1", 1 as "NULLCOL" ) "MDF1" ON ("TC1"."issouthindia" = "MDF1"."COL1") group by 1* is unnecessary, as it is performed with a temporary table that holds one static value, this can be replaced by a simple condition in the where clause as suggested. Hope this helps.
Denodo Team
12-10-2023 06:35:55 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here