Calculations in Sybase SQL Anywhere
Handling Sybase 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 Sybase SQL Anywhere 12.
By: Chrysanthus Date Published: 28 Aug 2012
Introduction
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;
The three variables should have been declared or created. Any 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, in the result pane. 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. However, we shall see how it can be saved later.
So you can do calculation on table cell values as the above example illustrates.
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, (NUMBER/ReorderLevel)*100 AS PercentAvailable
FROM Products;
The percentages (results) in this case will not be accurate. If you want the results to be accurate then you have to change the data types in the Products table for NUMBER and ReorderLevel to FLOAT (NUMERIC or REAL).
That is it for this part of the series. We stop here and continue in the next part.
Chrys