Hello there, I have a Question regarding the group by function in OQL. I am building A query where I am grouping a set of data. In the data I have an attribute that has multiple possible values that I need to convert to only two values (0 and 1). To convert this data I use a case statement in my select. For Example Select Case when Input = ‘Yes' or Input = 'Maybe' then 1 else 0 End as Result, Sum (Cost) From Example.Example Now I would like to group the select that I just created. My thinking was, that I could achieve this by just repeating the case statement in my group by. So I get the following: Select Case when Input = 'Yes' or Input = 'Maybe' then 1 else 0 End as Result, Sum (Cost) as Value From Example.Example Group by Case when Input = 'Yes' or Input = 'Maybe' then 1 else 0 End This Query works in SQL, however this seems to result in a Runtime error in OQL. Error: Caused by: org.postgresql.util.PSQLException: ERROR: column "Example.Input" must appear in the GROUP BY clause or be used in an aggregate function. This error can be resolved if I change the group by: Select Case when Input = 'Yes' or Input = 'Maybe' then 1 else 0 End as Result, Sum (Cost) as Value From Example.Example Group by Input This results in the folllowing Output: / What I want: Result Value Result Value 1 1 1 2 1 1 o 2 0 1 0 1 Although this query works, the result is that the group by statement now doesn't group the result of the case statement but my initial input. Is there way to solve this problem within OQL? I am in a situation where it's very preferable to solve this in OQL rather than making an new attribute on my entity.
asked
Tim Smit
1 answers
1
Hello Tim.
In your last query, are you able to group by Result rather than Input?
You could also try putting parenthesis around your last query and nesting it with another query.
SELECT Result, SUM(Value) FROM (your inner query) GROUP BY Result