JoSQL User Manual - Group By Clause

Group By Clause

The general syntax for the GROUP BY clause is:

GROUP BY Function | Accessor | Integer [ , Function | Accessor | Integer ]*

It is possible to perform grouping on the objects passed in via the "GROUP BY" clause, however it should be noted that since JoSQL has no "aggregate function" concept the operation of the GROUP BY works differently to normal SQL. Functions, integer column references and accessors can be used in the definition of the GROUP BY clause.

Firstly, if a GROUP BY clause is will mean that the List returned by Query.execute(List) will contain the columns indicated in the GROUP BY clause.

For example:

SELECT lastModified
FROM   java.io.File
GROUP BY name, length

Will cause a list of List objects to be returned that will contain the name and length rather than the columns rather than the lastModified value. The reason for this is that JoSQL goes one-step further than conventional SQL and will ALSO return the objects that matched each group by value. These results are stored as a Map within the Query object and can be retrieved by calling: Query.getGroupByResults(). The GROUP BY functionality is implemented this way since it is often useful to know which objects matched the key specified by the GROUP BY clause.

For example:

SELECT *
FROM   java.io.File
GROUP BY name, length

It is then possible to do:

List res = q.execute (myFiles);

Map gbRes = q.getGroupByResults ();

for (int i = 0; i < res.size (); i++)
{

    List key = (List) res.get (i);

    List files = (List) gbRes.get (key);

    for (int j = 0; j < files.size (); j++)
    {

        File f = (File) files.get (j);

        // Process the File object.

    }

}

You can also specify the columns to return in the SQL, in which for each of the objects JoSQL will retrieve the relevant value.

For example:

SELECT upper (name),
       formatDate (lastModified)
FROM   java.io.File
GROUP BY name, length

Will cause the group by results to contain a List of the relevant values, in this case the name of the file in uppercase and a nicely formatted date for the last modified time.

If you also specify an ORDER BY clause then JoSQL will order the results of each of the matching sets of objects.

For example:

SELECT name,
       length
FROM   java.io.File
GROUP BY name, length
ORDER BY upper (name), length DESC

Will cause the group by results to be ordered by the name (upper-case) and then by the length in descending order.

If you provide a LIMIT clause then JoSQL will apply that to the results, NOT to the group by results. Also, ALL the group by results will still be available in the Query object regardless of whether limiting has occurred on the results.

Care should be taken when using the group by clause however since re-writing the query can sometimes produce better performance, for example:

SELECT @avg_length
FROM   java.io.File
LIMIT 1, 1
EXECUTE ON ALL avg (:_allobjs, length) avg_length

is generally much faster than:

SELECT *
FROM   java.io.File
GROUP BY avg (:_allobjs, length, 'avg_length')

even though the same number of rows are returned. This is because JoSQL must perform grouping of the objects regardless of whether the avg function will produce a different result for each object (which it won't!). In short, case needs to be taken to understand whether you wish grouping of the objects to occur or do you just wish to execute "aggregate" functions on the objects, if you wish to just execute aggregate functions then use "EXECUTE ON", if you wish to actually group the objects according to unique keys use "GROUP BY".