A script-enabled browser is required for this page to function properly.

About Defined (and Computed) Columns

You can perform calculations on the data Query Builder retrieves from the database, then display the calculated data in the Results window as a new column (also known as a defined field). You can name, define, and display these columns just as you would any other column in your query.

You can use defined columns in a Query Builder query to construct "what if" scenarios, forecasts, projections, and other analyses, such as variances and percent-to-total.

Note: When you display a defined column (field) that contains an aggregate function, Query Builder automatically generates the corresponding GROUP BY clause for other displayed columns not involving aggregate functions. Advanced users can also specify GROUP BY columns manually for grouping by non-displayed columns.

While columns are generally synonymous with the columns of data stored in your tables, they can also refer to new columns of data in a query that are derived or calculated from the values in another column. These computed columns are sometimes known as derived or defined fields. For example, the Employee table may include two columns that describe compensation: Salary and Commission. You can create a defined column that combines the values in these columns into a new column called Net Pay that represents the summation of salary plus commission.

You can base your defined columns on any combination of single or multiple database columns, according to your needs.
Defined columns exist in your query, not in the database. You can choose to display them in the Results window as columns, or keep them hidden (such as when using them to create other defined columns).

Note: The defined column is only a part of your query and will not modify the table definition saved in the database.


Building a New Query

About the Query Window

Defined (and Computed) Columns Examples