Performing Math
September 13, 1998
Sometimes, it is useful to actually massage the
numerical data returned by performing basic arithmetic on the
results. SQL gives you plenty of useful tools for doing just this.
The most basic tools for arithmetic include the "+", "-", "*", and
"/" operators as you might expect.
This basic type of arithmetic is usually
performed in the
SELECT clause and usually involves creating
a new column based on the total achieved by doing the math.
For example, consider the following example in which we
subtract a 1.00 sale value to each of our
products,
add on the tax, and display the result in
a virtual column "REAL_PRICE".
|
Note that we call this a
virtual column because there is no "REAL_PRICE" column
in the actual table. It only exists in this view. Note also
that arithmetic can only be applied to numeric columns. Finally,
note that arithmetic follows the usual precedence rules. For example,
equations within parentheses are evaluated before they are applied to
equations outside of parentheses.
|
SELECT P_NUM,
P_PRICE,
REAL_PRICE = (P_PRICE - 1.00) +
((P_PRICE - 1.00) * .07)
FROM PRODUCTS;
The command will yield the following view
P_NUM P_PRICE REAL_PRICE
-------------------------------
001 99.99 105.92
002 865.99 925.54
003 50.00 52.43
-------------------------------
Another useful arithmetic tool is the
SUM operator that is used to total a column. The basic format
looks like:
SELECT SUM (column_name)
FROM table_name;
WHERE optional_where_clause;
For example, to get a SUM of all the products that
cost less than 100.00 you could use:
SELECT SUM (P_PRICE)
FROM PRODUCTS
WHERE P_PRICE < 100.00;
The command will yield the following view:
SUM (P_PRICE)
-------
149.99
-------
Order By
Introduction to Databases for the Web | Table of Contents
Maximums and Minimums
|
