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.
When you use a group by clause, the select clause cannot use the entities specified in the from clause What is available to the select clause has been, in effect, reduced using the group by clause. When you use a group by clause, the select clause can use only the group by criteria, and aggregation functions.Although the group by clause in this example reduces the result set to a list of zip codes, additional information from the query is internally available to the aggregation functions. You can use any of the standard group functions that are applicable, such as those used to calculate count, sum, average, maximum, and minimum.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: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:You can also use aggregation functions in the having clause in order to apply conditions on the whole group.
Copyright © TIBCO Software Inc. All Rights Reserved.