9. Calculated Fields

We have already seen column aliases which allow us to rename a field’s name in the query output. But we frequently encounter conditions which require changes to a field value. This is where the concept of a calculated field comes in.

9.1 Mathematical calculations

Any numeric field can be operated upon by mathematical operators we are all familiar with. We can add, subtract, multiply, divide and even find the remainder of a division operation fairly easily. While the operators supported differ in various implementations, the ones given below should be available across any RDBMS you come across.

Let us take our programming languages table and try to find out the decade in which the language was created. For example, Prolog was created in the 1970’s decade. Let us try to find out this fact from the year of creation available to us. One approach is to find the remainder of the year when divided by 10, which is the number of years in a decade. This is the value that specifies how many years has it been since the start of that decade.

1 select language, (year%10) remain from proglang_tbl;

Now if we subtract this value from the year of creation itself, we would get the decade in which the programming language was created.

1 select language, year-(year%10) decade from proglang_tbl;

Another approach is to divide the year by 10 and then multiply it by 10. This is slightly less straightforward because it relies on the definition of the integer data type. Since an integer cannot store decimal points, division by ten would silently chop off the remainder. 1972 divided by 10 would be 197 discarding the .2 bit. If we multiply this value by 10, we would get our desired decade value.

1 select language, (year/10)*10 decade from proglang_tbl;

9.2 String operations

By far the most commonly used string operation is concatenation. It means to join or combine strings. However since even numeric fields can be treated as a string, we can use the concatenation operator || on them too. See the example below to modify our decade field to include some characters.

1 select language, 'The '||((year/10)*10)||'s' decade from proglang_tbl;

Note that the concatenation operator manifests itself in different forms in different implementations. SQLite and Oracle use the shown || symbols whereas Ingres, MySQL and Microsoft SQL Server use + to denote concatenation. Their effect however is the same.

9.3 Literal Values

There are cases when one needs to use a fixed literal value as the values of a new column. Like column aliases can change the column header for readability, literal values change record values. In a sense they are not calculated fields, but fixed fields inserted in specific positions of a record. An example will help illustrate this - supposing to wish to really clarify that the year of language creation, as not just a number but also to include the characters AD

1 select language, year, 'AD' from proglang_tbl;

We can even use numeric literal values the same way, omitting the quotation marks for such values. A common utility for literal values arises when the user has to copy-paste data from their database query output into another tool like a spreadsheet or wordprocessor.