How do I... group by (a.leftYear = a.rightYear)

ge0ffrey · **Joined:** Thu Oct 13, 2005 4:19 am **Posts:** 98

Is it possible to write this query?

Code:

select
  (a.leftYear = a.rightYear),
  sum (a.someValue)
from A a
group by
  (a.leftYear = a.rightYear)

schauder · **Joined:** Thu Jul 05, 2007 9:38 am **Posts:** 287

Quote:

Is it possible to write this query?

You just did, so it is possible.

Or to rephrase: What do you want to know?
- What is the result?
- Will my database accept it (Oracle most probably won't)
- How to transform this into a valid HQL or Criteria Expression?

Enjoy

ge0ffrey · **Joined:** Thu Oct 13, 2005 4:19 am **Posts:** 98

schauder wrote:

Quote:

Is it possible to write this query?

You just did, so it is possible.

Or to rephrase: What do you want to know?
- What is the result?
- Will my database accept it (Oracle most probably won't)
- How to transform this into a valid HQL or Criteria Expression?

Enjoy

I was hoping to use it as a HQL query, but it generates a HQL grammar exception.
MySQL does except this query directly as SQL.

I've found a workaround by doing

group by case when (....) then true else false end