Difference between revisions of "MySQL Calculations and Concatenations"

Revision as of 19:08, 22 October 2007

When is retrieved from a MySQL database it is not always in the form we need it. For example, we may need to display a customer name and address as a single test string, but in reality the name and address are held in separate database tables. Similarly, a table might contain the price for a item and the shipping cost in two columns, when what we want is the result of the shipping cost and product cost added together. Fortunately MySQL allows fields retrieved from a table to be concatenated or used in calculations. In this chapter we will look at both approaches to manipulating data as it is extracted from a database.

Performing Calculations on Retrieved Data

Suppose we have a database table called shipping which contains product names, prices and shipping costs. The data contained in such a table might appear as follows:

SELECT * FROM shipping;
+------------+--------------------------+---------------+------------------+
| product_id | product_name             | product_price | product_shipping |
+------------+--------------------------+---------------+------------------+
|          2 | Microsoft 10-20 Keyboard |            25 |                7 |
|          3 | Apple iPhone 8Gb         |           400 |               13 |
|          4 | Dell XPS 400             |           900 |              100 |
+------------+--------------------------+---------------+------------------+
3 rows in set (0.00 sec)

Now lets assume that we need to extract the total cost of a product including the price and the shipping cost. To do so we can perform a calculation based on the product price and product_shipping columns and assign the result to an alias using the AS keyword:

SELECT product_name, product_price+product_shipping AS total_cost FROM shipping;
+--------------------------+------------+
| product_name             | total_cost |
+--------------------------+------------+
| Microsoft 10-20 Keyboard |         32 |
| Apple iPhone 8Gb         |        413 |
| Dell XPS 400             |       1000 |
+--------------------------+------------+
3 rows in set (0.00 sec)

In the above example we have performed a calculation (product_price+product_shipping) and assigned the value to an alias called total_cost. This is then displayed as part of our output.

MySQL supports a number of basic mathematical operators: