Calculations in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 14

Division 5

Forward: In this part of the series, we see how to do calculations in MySQL.

By: Chrysanthus Date Published: 7 Aug 2012

Introduction

This is part 14 of my series, Handling MySQL Events with Triggers and Stored Procedures Using SQL. You must have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we see how to do calculations in MySQL.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

The SET Statement
You can use the SET statement to do calculations. You can have something like:

SET answerVar = var2 – var1;

or

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.Qty, SaleDetails.SoldPrice, SaleDetails.Qty * SaleDetails.SoldPrice AS Total
FROM SaleDetails
JOIN Products
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.

So you can do calculation on table cell values as the above example illustrates.

Brackets
Use brackets as you use them in arithmetic BODMAS, so that whatever is in brackets should be done first.

Calculating Percentages
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 we 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
FROM Products;

The percentages (results) in this case may not be accurate. If you want more accurate results then you have to change the data types in the Products table for Quantity and ReorderLevel to NUMERIC or FLOAT.

That is it for this part of the series. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Calculations in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 14

Division 5

Introduction

Related Links

Comments