2 Replies Latest reply on Sep 20, 2006 5:58 PM by jgilbert

    Group By Error

    jgilbert
    jgilbert Sep 19, 2006 5:03 PM

      I get the following execption:

      java.sql.SQLException: Not in aggregate function or group by clause:


      when I try to run this EQL statement: 

      select p.agency.name, p.customer.name, sum(p.amount)
 from Payment p
 group by p.agency.name, p.customer.name
 having p.date between :startDate and :endDate


      If I change the group by statement to this: 

      group by p.agency.name, p.customer.name, p.date


      Then the error goes away, but obviously I don't get the result I wanted.

      Any thoughts? Am I doing something wrong? Or is this a bug?


      • 859 Views
      • Tags:
        • 1. Re: Group By Error
          epbernard
          epbernard Sep 19, 2006 7:32 PM (in response to jgilbert)

          As per the spec
          The HAVING clause must specify search conditions over the grouping items or aggregate functions that
          apply to grouping items.

          Same restriction as in SQL AFAIK

            • Actions
          • 2. Re: Group By Error
            jgilbert
            jgilbert Sep 20, 2006 5:58 PM (in response to jgilbert)

            Thanks!

            I changed the having to a where and everything is good.

             select p.agency.name, p.customer.name, sum(p.amount)
 from Payment p
 where p.date between :startDate and :endDate
 group by p.agency.name, p.customer.name


              • Actions