OQL Group by

0
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
2 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

answered
0

SELECT
    Client.Contact/FirstName AS FirstName,
    Client.Contact/LastName AS LastName,
    Client.Contact/EmailAdress AS EmailAdress
FROM
    Client.Contact
GROUP BY
    Client.Contact/FirstName,
    Client.Contact/LastName,
    Client.Contact/EmailAdress
HAVING
    COUNT(*) > 1   This was a query that worked for my goal to whom it may concern

answered