Having

Home > Query Language > Details of the Query Language > Having

The "having" clause is optional; it is a where clause for groups. Just as "where" limits objects, "having" limits groups. "having" search conditions are solely applied to results of aggregation functions (which also appear in the "select" clause).

Let's restrict our query result to groups with at least two persons. If there is born only a single person in a year, this group is not of interest for us.

Query(
   select( P(Person.P.yearOfBirth), countAgg() ),
   from(Person.CID),
   having(
      gt(
         castNumber(countAgg()), 
         V(1)
      )
   )
);

The new result looks like this:

Person.F.yearOfBirth()	countAgg()
1979	4
1983	5

Please note, that it's not possible to restrict field reference or path expression values in "having" clauses (even if those appears in the "select" clause). However, you can express the intended semantics with a "where" condition.

// the following statement is *not* legal
Query(
   select( P(Person.P.yearOfBirth), countAgg() ),
   from(Person.CID),
   having(
      gt(
         castNumber(P(Person.P.yearOfBirth)), 
         V(1979)
      )
   )
);

// however, the intended semantics can be expressed with a where condition
Query(
   select( P(Person.P.yearOfBirth), countAgg() ),
   from(Person.CID),
   where(
      gt(
         castNumber(P(Person.P.yearOfBirth)), 
         V(1979)
      )
   )
);

The first (illegal) statement would calculate the groups first and eliminate unneeded groups afterwards. The second (legal) statement calculates only the groups of interest. The result would be the same for both queries.

P(Person.P.yearOfBirth)	countAgg()
1983	5

Next chapter: Order By

Having

Labels