Group Statistics Example

The SQL Query feature in SuperMap allows grouping search results based on one or multiple specified fields, grouping records with identical values in the specified field(s).

Single Field Grouping

Using the Province_R dataset from the sample data PopulationAndEconomy as an example, this section demonstrates grouping statistics for province counts in eastern, western, central, and northeastern China.

  1. Open the PopulationAndEconomy sample data source in Workspace Manager.
  2. Select SQL Query from the Spatial Analysis tab -> Query group.
  3. In the SQL Query dialog, select PopulationAndEconomy as the data source and Province_R as the dataset.
  4. Click the Clear button in the expression toolbar to empty the expression input box.
  5. Check Query Properties in Show Results.
  6. Set Group Field to Class under Advanced settings.
  7. Enter Class, Count(SmID) as ProvinceClassCount in Result Reserved Fields.

    This preserves the Class field in result data. The system calculates province counts per group based on the Class grouping field and saves results in the ProvinceClassCount field.

  8. Click Query to display the result table window as shown:

Multi-Field Grouping

Select multiple fields in the Group Field dropdown or manually input them. The application will first group records by the first specified field, then subgroup by subsequent fields.

Continuing with the Province_R dataset, this example performs subgroup statistics based on region and map color.

  1. Open the PopulationAndEconomy sample data source in Workspace Manager.
  2. Select SQL Query from the Spatial Analysis tab -> Query group.
  3. In the SQL Query dialog, select PopulationAndEconomy as the data source and Province_R as the dataset.
  4. Click the Clear button in the expression toolbar to empty the expression input box.
  5. Check Query Properties in Show Results.
  6. Enter Class in Sort Field and select Ascending order. This sorts results by the Class field.
  7. Input Class,ColorID in Group Field. This creates primary grouping by Class and secondary grouping by ColorID.
  8. Enter Class, ColorID, Count(SmUserID) as ProvinceCount in Reserved Fields.

    This preserves Class and ColorID fields in results. The system calculates province counts per subgroup and saves results in the ProvinceClassCount field.

  9. Click Query to display the result table window as shown:

Related Topics

Build SQL Query

Construct SQL Expression Query

Common SQL Query Expressions

SQL Query Function Reference

Attribute Query Example

Join Query Example