You can group your SQL query by a particular column so that all rows that contain the same value are grouped together. Specify this column in the Group by column box in the SQL Select dialog box. When used in conjunction with aggregate functions, rows with the same value in all grouping columns are treated as a group. Duplicate rows are suppressed and aggregate values are reported for derived columns based on the aggregate functions. Specify the column(s) by name or by position number as listed in the Select Columns box. When using derived columns, you must specify the column number.
Along the same lines, you can tell MapInfo Pro to order the results of your query in a certain way. By default, MapInfo Pro orders records by their ascending value (which is alphabetical for character fields). When you list more than one column, MapInfo Pro orders the records by the first column. Records with identical values in the first Order by column are ordered by their values in the second Order By column. Like the Group By clause above, use column names or position number. For derived columns use the number. Do not use "col" with the position number.
Calculating Derived Columns
SQL Select can calculate derived columns and store the derived columns in the results table. A derived column is a special, temporary column that MapInfo Pro calculates on-the-fly, based on the contents of one or more columns that already exist in your base table.
For example, your table might contain the fields Purchases92 and Purchases93 (representing the total of each customer's purchases in 1992 and 1993, respectively). If you want your query results to show a Total Purchases column, showing each customer's combined purchases from 1992 and 1993, you can include a derived column in your Select Columns field.
In this example, the derived column would have the form:
Purchases92 Purchases93. Similarly, you may have a table of customer information that contains the fields Fname (representing the customer's first name) and Lname (the customer's last name). If you want your results table to include each customer's full name, you can include a derived column in your Select Columns field. In this example, the derived column might have the form:
Fname " " Lname. To specify a derived column, you enter an expression in the Select Columns field. A derived column expression is a combination of column names, operators (such as, and -), and functions (such as the UCase$ function, which converts a string column into uppercase).
If you have not already done so, you must enter one or more table names in the From Tables field before you specify derived columns.
To specify a derived column expression:
- In the SQL Select dialog box, click in the Select Columns field. The insertion point appears in the field.
- Delete the asterisk from the Select Columns field, if you have not done so already.
- Enter a column expression. The column expression should include one or more column names. If the expression includes more than one column name, the expression typically includes an operator (such as, or -) to combine the columns into one derived value. There are many different functions and operators that you can use in a column expression.
- Assign an alias to the column expression, if desired. To specify an alias, type a space after the column expression, then type the alias name in double quotation marks. Alias names are optional. If you give your column expression an alias, the alias will appear at the top of the column when you display the results table in a Browser window. If you do not assign an alias name, MapInfo Pro uses the content of the expression as the default alias (e.g. "Fname Lname").
- Specify additional column names or derived column expressions, as desired. If you enter additional column expressions, enter commas to separate the expressions.
The following example shows a derived column expression that performs addition, adding the values from two numeric columns. This example assumes that both Purchases92 and Purchases93 are numeric columns:
Select Columns: Purchases92 Purchases93 The next example shows the same column expression, with the addition of an optional column alias ("Net_Purchases"):
Select Columns: Purchases92 Purchases93 "Net_Purchases" The next example shows a derived column expression that performs division, dividing the contents of the Population column by one million:
Select Columns: Population / 1000000 "Millions" The next example shows a derived column expression that combines two string columns. This example assumes that both Fname and Lname are string columns:
Select Columns: Fname " " Lname "Full_Name" When the columns are string columns, the plus operator () performs string concatenation, rather than numerical addition. Thus, the expression Fname " " Lname produces a string consisting of a first name, followed by a space, followed by a last name.
The next example shows how a derived column expression can incorporate function calls. The Proper$ function operates on a string value and returns a string that has proper capitalization (only the first letter is capitalized).
Select Columns: Proper$( Fname " " Lname ) "Full_Name" The next example shows how to use the Format$ function to reformat numeric columns. Ordinarily, numeric columns do not include commas, which, unfortunately, make it hard to read large numbers. The following example uses the Format$ function to insert commas into the Purchases93 column. This example assumes that Purchases93 is a numeric column:
Select Columns: Format$( Purchases93, "$,#") "Purchases_1993" The string expression "$,#" tells the Format$ function to display a dollar sign before the column and to include commas in the column.
The next example shows how a derived column can calculate the geographic area of each row in the base table:
Select Columns: Area( Obj, "sq km") "Net_Area" Obj is a special column name, representing the geographical object that is attached to each row of the table.