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; +------------+--------------------------+---------------+------------------+ | 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:
Operator | Description |
---|---|
+ | Add |
- | Subtract |
* | Multiplication |
/ | Divide |