Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

It added every row individually. To add the totals of both columns for every row, you would use the

following statement:

SELECT (SUM(Column_1) + SUM(Column_2)) as Total FROM Orders

Your resultset would look like the following:

Total

13

The SUM() function totals the values of the column that is given as the argument. It is covered here

because it's addition—just on a larger scope.

A practical use of this function is when your application or Web page needs to have the total of one or

more of the columns from your table. Financial applications and Web site shopping carts are two prime

examples where something like this could be used. Remember, it is recommended that you give a

name to the return value of your function. If you don't, you will have to refer to your column by its

number rather than a name, which can be a little confusing when you try to use these operations in your

applications.

Subtraction

Subtraction works a lot like addition. You would use this function to subtract column values from each other.
A statement that uses subtraction would look like the following:

SELECT (Column_2 – Column 1) as Difference FROM Orders

This statement would return the difference of every row from the table individually. There is no function

for subtraction that would give you the difference of two columns together as with the SUM() function.

You could, however, total the two columns and then subtract them. Your statement would look like the

following:

SELECT (SUM(Column_2) – SUM(Column_1)) as Difference FROM Orders

This would accomplish the same thing.

Again, practical uses for the subtraction function would be Web carts and finance applications.

Multiplication

The multiplication lo function works the same way as the previous functions. The operator that is used for
multiplication is the asterisk (*), as in most programming languages. A multiplication function would look like
the following:

SELECT (Column_1 * Column_2) as Product FROM Orders

This statement would return the product of the two values for every row that exists in the table. To get

the product of the two columns, you would have to SUM() them, and then multiply them together as you

did for subtraction. It would look like the following:

SELECT SUM(Column1 * Column2) AS Product FROM Orders;

Note An important point to remember is that MySQL follows the normal mathematical

order of statements. The operations in parenthesis will be performed first,

followed by multiplication, division, addition, and then subtraction. Keep that in

mind when performing your mathematical operations. For example, 2+3*5 = 17,

not 25.

Division

MySQL also can perform division. It works the same way as the previous functions. A statement that uses
division would look like the following:

SELECT (Column_1 / Column_2) as Result FROM Orders

A NULL value will be returned if you try to divide by zero. Fractional values will be returned as a

decimal.

Modulo

The modulo function returns the remainder of two numbers. For example, if you were to divide 14 by 3, the
remainder would be 2. This is what modulo or MOD() returns.