MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

 » Database Journal Home » Database Articles » Database Tutorials MS SQL Oracle DB2 MS Access MySQL » RESOURCES SQL Scripts & Samples Tips » Database Forum » Slideshows
 Database Journal |DBA Support |SQLCourse |SQLCourse2

## SQL etc

Posted Sep 13, 1998

# Introduction to Databases for the Web: Pt. 2 - Page 6

By Selena Sol

## Performing Math

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
-------------``````

## Maximums and Minimums

Another couple of useful tools are the `MAX` and `MIN` operators that allow you to grab the boundary values in a column (alphanumeric). The most generic syntax follows something like:

``````SELECT MAX (column_name)
FROM table_name
WHERE where_clause [optional];``````

For example, consider the following case in which we grab the employee with the highest salary:

``````SELECT MAX (EMP_SALARY)
FROM EMPLOYEES;``````

In this case, we would get the following:

``````MAX (EMP_SALARY)
----------------
90000
----------------``````

Of course, you can also redefine the column name in the view by assigning the `MAX` or `MIN` value to a column name such as in the following case:

``````
SELECT top_salary = MIN (EMP_SALARY)
FROM EMPLOYEES
WHERE EMP_COMMISSION = '20%';``````

In this case, we would get the following:

``````top_salary
----------
40000
----------``````

## Counting Records

It is also very easy to count the number of records that meet a certain criteria. This function is performed with the `COUNT` operator and follows the syntax:

``````SELECT COUNT (column_name)
FROM table_name
WHERE where_clause [optional];``````

In other words, to count the number of employees in the EMPLOYEES table, you would use:

``````SELECT COUNT (EMP_NAME)
FROM EMPLOYEES;``````

which would return the following:

``````COUNT (EMP_NAME)
----------------
3
----------------``````

Note that sometimes, it is preferable to use `SELECT COUNT(*)` instead of specifying a `column_name`. This is because the `COUNT` operator does not consider columns with null values. Thus, if you specified a `column_name` and one of the rows in that table had a null value for `column_name`, your count would be off. Using "*" assures that all rows are counted even if they include null values. And by the way, most implementations of SQL will also require you to use the `DISTINCT` operator if you specify a `column_name`.

SQL etc Archives

Comment and Contribute

(Maximum characters: 1200). You have characters left.

 Latest Forum Threads SQL etc Forum Topic By Replies Updated MySQL rollback UAL225 0 August 21st, 09:56 PM Complex Search Query Galway 0 May 20th, 10:04 PM change collation at once supercain 2 May 15th, 06:18 AM SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM