Percentage Functions for DB2
By John Papiewski
IBM’s DB2 database management software uses the Structured Query Language, or SQL, to create and manage database structures and update the information in them. SQL is an industry-standard programming language that includes mathematical functions for performing detailed analyses on database data. Using SQL, you can easily compute percentages for each record of a database table, for groups of records and for table totals.
DB2’s SQL uses the SELECT statement to create reports from database information. The basic syntax of the statement is the word SELECT followed by a list of fields, table names and optional clauses such as WHERE and ORDER BY for testing and sorting data. The fields may come directly from a table or they may be calculated values based on table fields. You can, for example, list the contents of a orders table, including a product code, description, price and quantity, and you can multiply the price by the quantity to get the total.
The simplest form of percentage in a DB2 SELECT statement multiples one field by 100, then divides by a total to obtain the percentage. For example, the following SQL lists a customer name, order, item total and percentage of the total for the order:
SELECT name, order_number, item_total, item_total * 100 / order_total AS percentage FROM order_table;
If you calculate percentages based on integer fields, multiplying the first item by 100 ensures that a subsequent division returns a meaningful percentage. If you instead divide an integer by a bigger integer and multiply the result by 100, SQL returns a zero.
DB2’s version of SQL has a mathematical function called DECIMAL. Using it with a percentage function, DECIMAL gives you control over the number of decimal places of the result. The following SQL SELECT statement illustrates the use of DECIMAL in percentage calculations:
SELECT name, order_number, DECIMAL(item_total, item_total * 100.0 / order_total,5,2) AS percentage, order_total FROM order_table;
The DECIMAL function has three arguments: the number or calculated value, the number of digits for precision and the number of digits after the decimal. Here, the precision is five and the number of digits after the decimal is two. This SELECT statement produces a percentage as a number having five total digits and two decimal places, such as 56.05 or 199.10.
For frequently used, complex calculations that result in a percentage, consider using a UDF, or User-Defined Function. DB2 stores a UDF as an object, so it becomes part of the database along with fields, tables and other objects. The CREATE FUNCTION statement, followed by other SQL statements, writes a function entry in the database. Instead of repeating the complex calculations in your SELECT statements, you can refer to the function, making your programming simpler and more reliable.
Chicago native John Papiewski has a physics degree and has been writing since 1991. He has contributed to "Foresight Update," a nanotechnology newsletter from the Foresight Institute. He also contributed to the book, "Nanotechnology: Molecular Speculations on Global Abundance."