Open topic with navigation

TIBCO BusinessEvents® Enterprise Edition 6.3.1

# Group by Clause

The optional `group by` clause allows you to group entities that share one or more criteria into a single row. Each group is represented by one row.

This allows you to use any of the standard group functions that are applicable, such as those used to calculate minimum, maximum, count, sum, average.

Aggregation functions operate on all entities (and their attributes and properties) that make up a given group. For example, you could find out how many customers are in each zip code as follows:

    select c.zipcode from customer c group by c.zipcode;

Note that, although the `group by` clause reduces the result set to a list—in this example to a list of zip codes—additional information from the query is internally available to the aggregation functions.

## Group By Usage

The select clause can use only the group by criteria and aggregation functions.

For example, the following example is valid:

    select s.deptName, count(*)
      from /Student s
      group by s.deptName

However, the following example is invalid:

    INVALID  select s.deptName, s.deptNo, count(*)
               from /Student s
               group by s.deptName

In the second example, `s.deptNo` does not appear in the `group by` clause and therefore it cannot be used in the `select` clause.

## Using a Dummy Group Expression for Aggregation

Suppose you want to get a count of all entities in the `from` clause. In this case you must use a `group by` clause that creates a dummy group. In this case, all the rows are in the same group. As an example:

    select count(*)
      from /Student s
      group by 1

The group by clause restricts the columns that can be used in the select clause. So, as an example, this usage is invalid:

    INVALID  select s.deptName, count(*)
               from /Student s
               group by 1

Dummy groups are created when you specify a constant in the group by clause. For example, you can specify a dummy group in any of the following ways:

    group by ""
    group by 1
    group by 2
    group by "hello"

Any constant can be used.

## Optional having Clause

The optional `having` clause allows you to apply conditions after entities are grouped. For example this query returns the number of customers in each zip code, except for those zip codes where there are three or fewer customers:

    select c.zipcode, count(*) as count_zipcode
    from /customer c
    group by c.zipcode
    having count_zipcode > 3;

Note that the `having` clause accepts aliases declared in the `select` clause.

You can also use aggregation functions in the `having` clause in order to apply conditions on the whole group.

Did you find this helpful?

YesNo
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO BusinessEvents® Enterprise Edition

Copyright © 2004-2024. Cloud Software Group, Inc. All Rights Reserved.