Calculations and MySQL
Programming in MySQL – Part 10
Foreword: In this part of the series, I talk about calculations in MySQL.
By: Chrysanthus Date Published: 27 May 2015
The SET Statement
You can use the SET statement to do calculations. You can have something like:
SET answerVar = var2 – var1;
SET @answerVar = @var2 – @var1;
The two variables on the right hand side should have been declared with the DECLARE or SET statement. Any (or both) of the variables on the right hand side can be replaced with a literal number such as 25.
The SELECT Statement
You can also do calculations using the SELECT statement. Try the following SQL statement:
SELECT (8-5) AS Answer;
You should have 3, which is the answer, displayed. You do calculations with the SELECT statement and the alias name is like the result variable.
Table Cell Values
You will normally be doing calculations with table cell values. We saw an example before, which I repeat here:
SELECT Products.ProductName, SaleDetails.Quantity, SaleDetails.SoldPrice, SaleDetails.Quantity * SaleDetails.SoldPrice AS Total
ON SaleDetails.ProductID = Products.ProductID;
Here, for the last query result column display, the multiplication of quantity and unit price is given for each row. This value is not saved. You normally do not have to save computed value. The multiplication sign is * and not X.
So you can do calculation on table cell values as the above example illustrates.
Use brackets as you use them in mathematics BODMAS, so that whatever is in brackets should be done first.
To calculate a percentage, there has to be some whole quantity. You take the quantity of interest and put it over the whole, all that in brackets and then you multiply by 100. Imagine that you want to find the percentage of the quantity in stock with reference to the reorder level in the products table. This problem means you have decided to take the reorder level as a whole. It is you to decide what your whole is and then you establish the percentage problem.
Read and try the following code, which illustrates this.
SELECT ProductName, (Quantity/ReorderLevel)*100 AS PercentAvailable
That is it for this part of the series. We stop here and continue in the next part.
Related LinksImplementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course