Creating Column Aliases - MapInfo_Pro - 2023

When MapInfo Pro creates a derived column, it uses the expression itself as the name for that derived column, which can be awkward. You can, however, specify an alias for the column. For example, the population density column in our DENSITY Browser was titled:

• Population / Area(obj, "sq mi")

To rename the column, add the new name to the Select Columns box when listing the columns.

The alias must follow the expression. It must also be separated from the expression by a blank space and enclosed in quotes. For example:

• Country, Population / Area(obj, "sq mi") "POP_DENSITY"

When MapInfo Pro creates the temporary query table, the population density column will now be named POP_DENSITY.

You can use aliases to rename any column in a table, not just derived columns. For example, if each country in your table is a separate sales territory for your corporation, you might want to rename the Country column "TERRITORY". The procedure is identical:

• Country "TERRITORY", Population / Area(obj, "sq mi") "POP_DENSITY"

Example - Computing Total World Population Density

In this example, we demonstrate several SQL Select features:

• Create derived columns based on an aggregate function.
• Rename columns by typing column aliases.
• Create a new results table based on the WORLD table.
• Perform SQL Select on the new table.

In this example, you have a table of world population statistics (WORLD). You would like to create a table that shows the population density of the entire world. Use the following formula:

• TOTAL POPULATION / TOTAL AREA

While the WORLD table does not have total population or total area columns, we can derive them by applying the sum() aggregate function to the Population column and the Area function. We will do this in our first query.

1. Open the WORLD table, and maximize its display.
2. On the MAP tab, click SQL Select to display the SQL Select dialog box.
3. In the From Tables box, choose WORLD from the Tables drop-down list .
4. In the Select Columns box, delete the * (keep the cursor in this box for the next steps).
5. From the Aggregates drop-down list, select Sum.
6. From the Columns drop-down list, select the population column. The cursor is now on the inside of the end parenthesis [)]. Move it to the outside using the Right Arrow key.
   Note: Sum (Population) creates a derived column to compute the total world population.
7. Press the Spacebar once and type the column alias "Total_Pop" with quotation marks. Remember to always use a blank space to separate an alias from its expression.
   Note: The Sum(Population) column will appear in the results table as Total_Pop.
8. From the Aggregates drop-down list, select Sum.
9. From the Functions drop-down list, select Area. The cursor is now on the inside of the end parenthesis [)]. Move it to the outside with the Right Arrow key.
10. Sum(Area) creates a derived column to compute the total area.
11. Press the Spacebar once and type a column alias "Total_Area" with quotation marks.
    Note: The Sum(Area(obj, "sq mi")) column will appear in the results table as Total_Area.
12. Leave the Where Condition, Group By Columns, and Order By Columns fields blank.
13. In the Into Table Named box, type TOT_POP_AREA as the name for this table.
14. Click Verify.
15. Select the Browse Results check box.
16. Click OK.

    MapInfo Pro produces a query table named TOT_POP_AREA containing two columns. The first column shows a value for the total population and the second column shows a return value for the total area.

    
    

    

    
    

    Now that we have the total population and total area, we can compute the world's population density by performing an SQL Select on the TOT_POP_AREA table we just created.

In this SQL Select, we will divide the total population by the total area.

1. On the MAP tab, click SQL Select to display the SQL Select dialog box.
2. In the From Tables box, delete the information from our last query.
3. From the Tables drop-down list, select TOTAL_POP_AREA.
4. In the Select Columns box, delete the information from our last query.
5. From the Columns drop-down list, select Total_Pop . Remember, this is the column from our last query.

   Notice that when you select Total_Pop, COL1 appears in the Select Columns field. This is the column number of that field. When choosing derived columns from a previous query, MapInfo Pro uses the column number instead of the name. Columns can be referred to by name or number, where the number designates the order the column has in the Select Columns box. COL1 and COL2 refer to the first and second columns, respectively.

6. From the Operators drop-down list, select the division sign (/).
7. From the Columns drop-down list, select Total_Area. COL2 displays in the Select Columns field.
8. Leave the Where Condition, Group By Columns, and Order By Columns fields blank.
9. In the Into Table Named box, type WORLD_DENSITY as the name of this table.
10. Click Verify.
11. Select the Browse Results check box.
    
    

    

    
    
12. Click OK.

    MapInfo Pro produces a query table named WORLD_DENSITY that will return a value for the population density in people per square mile for the entire world.