# 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

**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

### Related Links

Implementing Database in MySQLProgramming in MySQL

Backup Basics in MySQL

MySQL Access Privileges

Regular Expressions in MySQL

Date and Time in MySQL

Event in MySQL

MySQL Transaction

PurePerl MySQL API Prepared Statements

**More Related Links**

PurePerl MySQL Command Line Tool

Major in Website Design

Perl Course - Optimized

Web Development Course

**BACK**

**NEXT**