Grouping

Use SQL to write queries that group results. Grouping is a powerful facility for summarizing data, particularly in combination with aggregates such as COUNT or SUM.

The following query uses grouping and aggregates: 
SELECT COUNT(name), population FROM cities GROUP BY population;

This query returns the number of cities that have the same population for each distinct population value that appears in the table. The GROUP BY clause breaks the set of result rows into groups with equal populations; then the target list is evaluated for each group separately. The COUNT aggregate counts the number of city names in the group.

Consider whether any of the types you define are candidates for grouping. In the SimpleMap DataBlade® module, for example, polygons are a poor candidate; users seldom want to group geographic data that contains identical polygons.

You can group results by using complex expressions. For example, the following query divides cities into groups that are within 10 units of the same area and then adds the population for the group: 
SELECT Area(boundary) / 10 AS dimensions, SUM(population) 
   FROM cities GROUP BY dimensions;
To determine whether your DataBlade module requires support routines for grouping, ask the following questions:
  • For each type in the DataBlade module, can the values sensibly be broken into groups that are equivalent?
  • What is the meaning of each of these groups?
  • Do users want to group values in that way?