Not a group by expression : error

Question

I want to query on a table keeping few fields as disctict. So, I use group by on the fields to be distinct. BUt HANA system throws an error: Not A Group By expression

select "REFID", "ORGID_WE", "ARTIKLE", "ORGID", "HELLER", "WERT", "BZBAS_AW" 
from "SYSTEM"."SOFT"
GROUP BY "REFID", "ORGID_WE", "ARTIKLE", "ORGID", "HELLER" ;

For me this looks good. Any ideas??

Removed the MySQL tag because MySQL does not generate this error. I removed the SQL Server tag because it is also not about SQL Server. — Gordon Linoff, Jan 07 '14 at 16:56
This is basically SAP HANA. But i thought any guys with well verse knowledge on SQL might give some idea :) — Sangamesh Hs, Jan 07 '14 at 16:57
What are you missing about the error message? You have the columns `WERT` and `BSBAS_AW` in the `select` list, but they are not in aggregation functions (`min()`, `max()`, etc.) and they are not in the `group by`. — Gordon Linoff, Jan 07 '14 at 16:57
ERROR claims/model1/claims.procedure Error creating procedure;not a GROUP BY expression: 'SOFT.WERT': line 7 col 87 (at pos 429) at ptime/query/checker/check_expr.cc:7085 ERROR claims/model1/claims.procedure Object potentially broken due to transaction rollback. ERROR exception 40177: repository/base/activation/activator.cpp:835 Object potentially broken due to transaction rollback. — Sangamesh Hs, Jan 07 '14 at 16:59
It's not a "group by" expression. "group by" is used against aggregate queries. Maybe you meant to use "order by"? — Jeremy Holovacs, Jan 07 '14 at 17:01
I understood that group by is nothing but the altenative for distinct. Isn't that true? — Sangamesh Hs, Jan 07 '14 at 17:05

score 2 · Accepted Answer · answered Jan 07 '14 at 16:59

2

try using aggregate functions:

select "REFID", "ORGID_WE", "ARTIKLE", "ORGID", "HELLER", sum("WERT"), sum("BZBAS_AW")
from "SYSTEM"."SOFT"
GROUP BY "REFID", "ORGID_WE", "ARTIKLE", "ORGID", "HELLER" ;

answered Jan 07 '14 at 16:59

Brett Schneider

3,993
2
16
33

That works. BUt could you please tell me why you used sum in there just to make myself clear about my understanding!! – Sangamesh Hs Jan 07 '14 at 17:04
1

`sum()` is just arbitrary. you may as well use any aggregate function. the thing is when you group by `"REFID", "ORGID_WE", "ARTIKLE", "ORGID", "HELLER"`, the combination of those in the result set are made unique. this may lead to duplicates of `"WERT"` and `"BZBAS_AW"`, which need to be aggregated. you can choose to sum them, average them, take the max or min, count them. see here for more detailed explanations: http://en.wikipedia.org/wiki/Aggregate_function – Brett Schneider Jan 07 '14 at 20:25

Not a group by expression : error

1 Answers1