Home > Query Language > Details of the Query Language > Select and Group By

The "select" clause is optional. If it is omitted, there are instances of the investigated entity class extent returned. Otherwise you are able to project the result to certain fields or path expressions of returned instances.

A "select" clause is made up of one or more field references and/or path expressions which all start from the same point of origin. So if you query for persons, all field references/path expressions of the "select" clause start with "P(Person.P.". Here's a simple example:

// Java Persistence Query Language
SELECT p.oid, p.firstname
FROM Person p

// OOMEGA Query Language
Query(
   select(
      P(Person.P.oid),
      P(Person.P.firstname)
   ),
   from(Person.CID)
);

The return type of such a query is a collection of "List<Object>" objects (instead of a collection of "Person" objects). Each list contains two objects: (1) the object identifier and (2) the person's firstname.

Please have a look at the following code snippet:

Query(
   select(
      P(Person.P.oid),
      P(Person.P.firstname),
      nav( P(Person.P.birthplace), P(Person.P.street) ),
      nav( P(Person.P.children), P(Person.P.children) )
   ),
   from(Person.CID)
);

Again, the return type of the query is a collection of "List<Object>" objects. Now each list contains four objects: (1) the object identifier, (2) the person's firstname, (3) the person's birthplace and (4) its grandchildren. Please note that you are able to specify path expressions like "nav( P(Person.P.birthplace), P(Person.P.street) )" and "nav( P(Person.P.children), P(Person.P.children) )" in your "select" statements.

Aggregate functions: avgAgg, sumAgg, minAgg, maxAgg, countAgg

"avgAgg", "sumAgg", "minAgg" and "maxAgg" are aggregate functions which can be applied to numeric fields like "P(Person.P.yearOfBirth)".

Query(
   select(
      avgAgg( P(Person.P.yearOfBirth) )
   ),
   from(Person.CID)
);

• "avgAgg" returns the mean average of the field's values.
• "sumAgg" returns the sum of the field's values.
• "minAgg" returns the lowest value of the field's values.
• "maxAgg" returns the highest value of the field's values.

"countAgg" is an aggregate function, but it is not applied to an existent field. Rather "countAgg" introduces a new field which represents the total number of results.

Query(
   select(
      countAgg()
   ),
   from(Person.CID)
);

All aggregate functions return a single numeric value. If there are additional field references and/or path expressions added to the "select" clause, then the aggregate functions are applied to the respective groups.

Grouping

Grouping occurs automatically if aggregate functions as well as field references/path expressions are part of one and the same "select" clause. The "select" clause then contains the grouping criteria and at least one aggregation function. Returned objects are allocated into different groups - subject to the grouping criteria. Now aggregate functions are not applied to the whole result set but to individual groups.

If you group by the field "P(Person.P.yearOfBirth)", then persons with the same year of birth are allocated to the same group.

Query(
   select(
      P(Person.P.yearOfBirth),
      countAgg()
   ),
   from(Person.CID)
);

The persons are grouped by the year of birth. The "countAgg" function counts the number of persons in each group. Please have a look at the following example query result.

Next chapter: Where