Using SQL Select to Query Data - MapInfo_Pro - 2023

A selection is a subset of data rows in a table, chosen based on the contents of one or more columns from the table. You create selections by formulating questions, or queries, about your data. How many customers live in the state of Idaho? Which police precinct has the highest rate of violent crime? MapInfo Pro stores the results of these questions in temporary tables called query tables.

While MapInfo Pro's Select command lets you formulate sophisticated queries, SQL Select goes even further. The records in the query table generated by the Select command do not have any information in them that is not contained in the records of the base table. They are, in fact, the same records. They have been arranged so that they can be viewed together.

With SQL Select you can create query tables containing information that was only implicit in the base table(s).

The SQL Select dialog box is one of the most elaborate ones in MapInfo Pro. But do not be intimidated. Once you learn what each box is used for, it is fairly simple to create powerful selection statements. You can type directly into the boxes or you can use the pop up menus on the right to enter items into the boxes.

The easiest way to describe the dialog box is to walk you through an example, step-by-step. This example uses data from the WORLD table included in MapInfo Pro, so you can try out this SQL Select exercise yourself. We have included a brief description of the parts of the dialog box with each step. A complete description of each box is given after the example.

Example 1 - Computing Population Density Using Area

In this example, we will use the world map to create a table of all the countries that have a population density of over 500 people per square mile.

The following formula computes population density:

• POPULATION / AREA

While the WORLD table has a population column (pop_1994), it does not include a column for country area. However, since the WORLD table has graphic objects associated with the records, MapInfo Pro can compute the total area for each country.

Open the WORLD.TAB table, and maximize its display by clicking the window's maximize button. On the MAP tab, click SQL Select to display the SQL Select dialog box. You can also reach the dialog box from the TABLE and SPATIAL tabs.

To complete the SQL Select dialog box:

1. In the From Tables box, choose WORLD from the Tables drop-down list.

   You can specify more than one table in an SQL statement. This example uses only one table, WORLD.

   You should fill in the From Tables box before you fill in the Select Columns box. MapInfo Pro uses the tables in the From Tables box to generate the list of columns in the Columns drop-down list.

2. Place your cursor in the Select Columns box and delete the asterisk (*). In this example, we will specify a list of columns to be included in the resulting query table, instead of including all columns.
   Note: An asterisk (*) in the Select Columns box means by default that all columns would be queried.

   Remember, the query table is the temporary table that MapInfo Pro creates to store the results of the query.

3. Select Country from the Columns drop-down list.
4. Select pop_1994 from the Columns drop-down list.

   Now, we need to compute population density. Remember, population density is computed by dividing population by area. There is no Area column in the table. However, there is a function, Area, which will compute the area of any mappable object. Since the WORLD table is mappable, MapInfo Pro can compute the area of each country and, therefore, the population density of each country.

5. Select the division sign (/) from the Operators list.
6. Select Area from the Functions popup.

   This creates a derived column. A derived column is a column that contains the results of calculations performed on another column or columns. When MapInfo Pro creates the query table, it will include two columns: the Country column and the pop_1994/Area(obj, "sq mi") column, which is our population density column. MapInfo Pro includes a units statement with all geographic functions. If you wanted MapInfo Pro to return the area in square kilometers, you would change "sq mi" to "sq km."

   Now, we need to build an expression that selects only those countries whose population density is over 500 people per square mile.

7. Tab to the Where Condition box and select pop_1994 from the Columns drop-down list.
8. Select the division sign (/) from the Operators drop-down list.
9. Select Area from the Functions pop up.
10. Select the greater than sign (>) from the Operators drop-down list.
11. Type the number 500.

    We have now built the expression "Select all countries whose population density (pop_1994/Area(obj, "sq mi")) is more than (>) 500 people per square mile."

12. Leave the Group by Columns box blank.
13. Tab to the Order by Columns box and select Country from the Columns list.

    The Order by Columns box allows you to specify the order the records in the query table will display. By selecting Country, MapInfo Pro will list the records in alphabetical order, according to country name.

14. Tab to the Into Table Named box and type DENSITY.

    By default, MapInfo Pro names the query table Query1. Subsequent query tables will be named Query2, Query3, and so on. You can change the name of the query table by typing in a new table name in the Into Table Named box. MapInfo Pro will name the query table DENSITY.

15. Click Verify.

    MapInfo Pro checks the syntax of your SQL statement. If there are any errors in your statement, MapInfo Pro gives you an error message telling you what the error is and which box contains the error.

16. Select the Browse Results check box to create a Browser of the query table. If you do not select Browse Results, MapInfo Pro still creates the temporary query table but does not display it. If you wanted to display the table after the fact, on the TABLE tab, in the Content group, click New Browser, and select DENSITY from the list.
17. To display the query results in the currently active Map window, select Find Results in Current Map Window. If there are no open Map windows, this check box is disabled. You cannot save this check box setting to a template. This option is cleared by default.
    
    

    

    
    
18. Click OK to create the following table.

    The first column contains the country name. The second column contains the population density. Note that the countries are listed in alphabetical order (Order By: Country) and the population density for each country listed is over 500 people per square mile (pop_1994/ Area(obj, "sq mi") > 500).

Example 2 - Computing Population Density with SQL

To set up an SQL Select statement that selects only those states with a high population density, do the following:

1. Open the STATES table in a Map window.
2. On the MAP tab, click SQL Select and fill in the SQL Select dialog box. You can also reach the dialog box from the TABLE and SPATIAL tabs.
   • Select Columns: *
   • from Tables: States
   • where Condition: (Pop_1990 / Total_Area) > 500
   • into Table Named: HIDENSITY

   This creates a new table, HIDENSTY, that contains only those states with a relatively high population density (population divided by total area). The asterisk (*) in the Select Columns box transfers all of the columns in the STATES table to the HIDENSTY table.

3. On the HOME tab, click Save Copy As and save the Selection table. The table can be saved with any filename.

Examples of Group by Columns

Imagine that you have a table of customer orders. Each row in the table represents a single order. One column in the table contains the name of the sales representative who booked the order, another column contains the name of the customer, and yet another column contains the order amount.

For each sales representative, you want to find out:

1. The number of orders booked by that representative;
2. The average order amount entered by that representative;
3. The total value of the orders entered by that representative.

The following SQL Select query produces the desired results:

Notice the Group By Columns field and the three aggregate operators in the Select Columns field. What MapInfo Pro does is:

1. Find all the rows for a particular sales representative.
2. Count the number of rows: Count(*).
3. Calculate the average value of orders for the representative: Avg(AMOUNT).
4. Calculate the total value of the orders for the representative: Sum(AMOUNT).

MapInfo Pro does this for each sales representative and produces a results table that has a single row for each representative. The aggregate operators (Count, Avg and Sum) subtotal the data values for all of the rows having the same value for Sales_Rep.

Consider this SQL Select:

This is essentially the same query as the previous one, except that we are grouping by Customer rather than by Sales_Rep. This SQL Select finds the count, average, and sum of orders for each customer rather than for each sales representative.

The following example demonstrates a multi-column grouping:

We have specified two column names in Group By Columns. In this case, MapInfo Pro groups rows first by sales representative and then by customer. The result table for this query has one row for each different customer/sales representative combination. When a particular customer has ordered through two or more sales representatives, there is a row summarizing that customer's business with each sales representative. The rows are grouped first by sales representative and then, for each representative, by customer.

Example - Using the Group by Columns Field to Subtotal the Results Table

The Group By Columns field in the SQL Select dialog box is optional. If you enter one or more column names in this field, the results table will contain subtotals, or aggregate information, for the table.

When you specify a Group By Columns value, MapInfo Pro queries the specified column(s) to see which rows have the same value in that column. Thus, if you have a Customers database, and you Group By the StateName column, MapInfo Pro places all Texas customers in one group, while placing all California customers in another group, etc. MapInfo Pro then calculates aggregate information (sums, averages, etc.) for each group.

To specify subtotal criteria:

1. In the Group By Columns field, enter the name or number of the column(s) MapInfo Pro should use to calculate subtotals.

   For example, if you want to total your Customer table on a state-by-state basis, enter the column name (e.g., StateName) representing the customer's state.

2. Enter the same column name (e.g., StateName) in the Select Columns field.
3. Enter one or more aggregate operators (Sum, Count, Avg, Min, or Max) in the Select columns field. Remember to separate the aggregate operators with commas.

For example, if you want to count how many customers are in each state, enter the expression: Count(*). If you want to calculate the total of all sales within each state, enter an expression such as Sum(sales) (where Sales is the name of a column).

All columns in the Select Columns field that are not based on aggregate functions must be listed in the Group By Columns field. These are the columns MapInfo Pro examines to determine the groupings. Each set of unique data values on these columns has a single row in the query table. Derived columns should be designated by numbers indicating relative position: "1," "2," "5," for first, second, and fifth column, respectively.

For example:

This query tells MapInfo Pro to count all the rows recorded for a particular month and produce a query table grouped by month. The results table has one row per month, and that row has a column indicating how many people were sick during that month.

In the Group By Columns field, you should refer to columns by their name or number (where 1 represents the first column listed in Select Columns field). When you are not using a join, you can use regular field names. When you are grouping by the value of a derived column or if you are joining tables, you must use a column number rather than a column name. When referring to columns by number, do not prefix the number by "col."

You can enter more than one column in the Group By Columns field. MapInfo Pro first groups rows by the first column you list. Within those groupings, MapInfo Pro groups rows by the second column, and so forth. For each resulting row, the query table contains aggregate values for all columns based on aggregate functions.

Example - Using the Order by Columns Field to Sort the Results Table

The Order By Columns field in the SQL Select dialog box lets you sort the rows of the results table. Sorting affects the top-to-bottom order of how rows appear in a Browser window. You may want to sort your results table if you intend to print the Browser window as a report.

The Order By Columns field is optional; if you leave the field blank, the results table is not sorted. If you enter a column name in the Order By Columns field, MapInfo Pro sorts the results table according to the contents of that column.

Performing Multi-Level Sorts

Under some circumstances, you may need to specify two or more columns in the Group By Columns field. For example, if you sort the City_1K table according to the State column, the results table is sorted by state name; however, within the group of rows for California, the cities are not sorted, San Francisco appears above Anaheim. To correct this problem, you can enter two column names in the Order By Columns field:

When the Group By Columns field contains two or more column names, MapInfo Pro performs a multi-level sort. In the preceding example, MapInfo Pro first sorts the City_1K table by the contents of the State column; then, for each group of rows having the same State name, MapInfo Pro performs a secondary sort. The secondary sort alphabetizes the rows within each state according to city. The row for California, Anaheim now appears above the row for California, San Francisco.