Aggregating Data - MapInfo_Pro - 2023

When you aggregate data, you perform a mathematical operation on all of a column's values in all of the records in your table. Unlike the Select command, which only allows you to perform mathematical functions on individual records, SQL allows you to aggregate (or summarize) data across records.

MapInfo Pro looks for each unique set of data values in the specified column or columns and creates one row for each such unique set. When you aggregate data, you need to specify:

• How the records will be grouped.
• How the data will be aggregated (summarized).

For example, you have a table of sales representatives and their sales figures for the past three months:

MapInfo Pro could also compute the total sales for each representative by specifying in the SQL Select dialog box:

• Select Columns: SALES_REP, sum(SALES)
• Group by Columns: SALES_REP

  SALES_REP	sum(SALES)
  John	3300
  Cathy	3000
  Julie	2700

or MapInfo Pro could compute the average sales for each representative:

• Select Columns: SALES_REP, avg(SALES)
• Group by Columns: SALES_REP

  SALES_REP	avg(SALES)
  John	1100
  Cathy	1000
  Julie	900

or MapInfo Pro could compute the total sales for each month:

• Select Columns: MONTH, sum(SALES)
• Group by Columns: MONTH

  MONTH	sum(SALES)
  May	3200
  June	2900
  July	2900

MapInfo Pro has six aggregate functions:

• Count(*): counts the number of records in a group. It takes * as its argument because it applies to the record as a whole, and not to any particular field in the record.
• Sum (expression): calculates the sum of the values in <expression> for all group records.
• Average (expression): calculates the average of the values in <expression> for all the records in a group.
• WtAvg (expression): calculates the weighted average of the values in <expression> for all the records in a group.
• Max (expression): finds the highest value in <expression> for all records in a group.
• Min (expression): finds the lowest value in <expression> for all records in a group.