MySQL Calculations and Concatenations

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;

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:

mysql> 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)