SQL Functions   «Prev  Next»
Lesson 7 Arithmetic functions
Objective Understand the purpose of arithmetic functions.

SQL Arithmetic Functions

Numeric, or arithmetic/mathematical, functions are really helpful as you begin summarizing information in your tables. This will often be the case when you are building queries to support reports, you will be totalling groups of rows, categorizing information, and so on.
The numeric functions let you do more than just add and subtract values. They also let you do things like averaging all values for a given column, totaling all values, getting the maximum or minimum values for a given column, and more.
You may recognize these functions as the same as those you use with many worksheet applications. They are nearly identical and operate in the same manner as you would expect. Just remember that you can assign column names to these results and that you can apply them in
GROUP BY

situations, giving you subtotals for results sets. The next several lessons cover the most commonly used functions.
Here are some examples showing usage of SQL Arithmetic Operators:

SQL> select 10+ 20;
+--------+
| 10+ 20 |
+--------+
|     30 |
+--------+
1 row in set (0.00 sec)

SQL> select 10 * 20;
+---------+
| 10 * 20 |
+---------+
|     200 |
+---------+
1 row in set (0.00 sec)

SQL> select 10 / 5;
+--------+
| 10 / 5 |
+--------+
| 2.0000 |
+--------+
1 row in set (0.03 sec)

SQL> select 12 %  5;
+---------+
| 12 %  5 |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Calendar Tables

Another useful auxiliary table is a calendar table. One type of calendar table has a primary-key column that contains a row for each calendar date (past and future) and other columns that indicate the date's attributes:
  1. business day,
  2. holiday,
  3. international holiday,
  4. fiscal-month end,
  5. fiscal-year end,
  6. Julian date, and
  7. businessday offsets.

Another type of calendar table stores the starting and ending dates of events (in the columns event_id, start_date, and end_date, for example). Spreadsheets have more datearithmetic functions than DBMSs, so it might be easier to build a calendar table in a spreadsheet and then import it as a database table.
Even if your DBMS has plenty of datearithmetic functions, it might be faster to look up data in a calendar table than to call these functions in a query.