SQL Query Grouping Statistics Example

The SQL Query function in SuperMap can group the Search Result according to the specified field (s), so that the records with the same value on the Specify Field are grouped together.

Specifies a single field grouping

The following will take the Province _ R data in the Sample Data PopulationAndEconomyDatasource as an example to group the number of provinces in the eastern, western, central and northeastern regions of China.

  1. Open Population AndEconomyDatasource from Sample Data into Workspace Manager.
  2. Select SQL Query in the Spatial Analysis tab-> Query group.
  3. In the SQL Query dialog box, select Population And Economy, Dataset SelectProvince _ R.
  4. Select the SQLEdit check box and clear the contents of the SQL statement input box.
  5. Set Group Field to: Class . under Advanced Node
  6. Enter: Class, Count (SmID) as ProvinceClassCount in Result Reserved Fields.

    Indicates that the Class field is retained in the Result Data. Calculate the number of provinces in each group according to the Group Field Class, and save the result in the ProvinceClassCount field.

  7. In Show Results, check Browse Attribute Table.
  8. Select Save Results to set the Datasource and Dataset Name where the Search Result is located.
  9. Click Query to pop up the Result Table window. The contents displayed in the window are shown below:

Specify multiple field groupings

Simply check the Group Field drop-down check box or enter multiple Select Fields directly, and the program will first group the property sheet by the first field entered, then by the second field, and so on.

The following still takes the Province _ R data in the Sample Data PopulationAndEconomyDatasource as an example, based on the number of provinces in each region and then according to the color of the map for grouping statistics.

  1. Open Population AndEconomyDatasource from Sample Data into Workspace Manager.
  2. Select SQL Query in the Spatial Analysis tab-> Query group.
  3. In the SQL Query dialog box, select Population And Economy, Dataset SelectProvince _ R.
  4. Select the SQLEdit check box and clear the contents of the SQL statement input box.
  5. Enter: Class in the Sort field and select Sort By Ascending. Indicates that the result will Ascend based on the Class field.
  6. In the Group Field, enter: Class, ColorID. Indicates that after grouping based on the Class field, it is grouped based on the ColorID Field.
  7. In the reserved Result Field, enter: Class, ColorID, Count (SmUserID) as ProvinceCount.

    Indicates that Class, ColorID Field is retained in the result. Calculate the number of provinces in each group according to Class and ColorID Field, and save the Calculate Result in the Province ClassCount field.

  8. In Show Results, check Browse Attribute Table.
  9. Select Save Results to set the Datasource and Dataset Name where the Search Result is located.
  10. Click Query to pop up the Result Table window. The contents displayed in the window are shown below: