Calculations and MySQL

Programming in MySQL – Part 10

MySQL Course

Foreword: In this part of the series, I talk about calculations in MySQL.

By: Chrysanthus Date Published: 27 May 2015

Introduction

This is part 10 of my series, Programming in MySQL. In this part of the series, I talk about calculations in MySQL. You should have read the previous parts of the series before reaching here, as this is a continuation.

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.Quantity, SaleDetails.SoldPrice, SaleDetails.Quantity * 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. The multiplication sign is * and not X.

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

Brackets
Use brackets as you use them in mathematics 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 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
FROM Products;

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

Chrys

Broad Network

Related Articles

Calculations and MySQL

Programming in MySQL – Part 10

MySQL Course

Introduction

Related Links

Comments