Queries with Calculated Fields in SQLite
Another useful thing that can be done with select queries are mathematical calculations with or without using data from your database tables. The example below could have probably have been done more easily on a calculator but it demonstrates the principle.
sqlite> select ((12 *12)/(17-11)+1);
((12 *12)/(17-11)+1)
25
sqlite>
Notice that the header above the result is the equation itself. By default the headers will be the column name or in the case of a calculated field the equation. A more descriptive term can replace that header by following the column name or equation by one or more spaces and the desired text for the header in single quotes
sqlite> select ((12 *12)/(17-11)+1) 'Math Result';
Math Result
25
sqlite>
In this example we want to calculate the dollar value of the items listed in the inventory table. In order to do this it will be necessary to multiply the price per item by the quantity on hand of that item. If you look carefully at the inventory table you will see that only the pack cost is listed not the per unit cost. To calculate the unit cost, the Pack Cost must be divided by the Pack Quantity. The value of each list item can then be calulated by multiplying the unit cost by the quantity on hand. Notice that the ROUND function is used to round the results to two decimal places. The format for this function is
ROUND(Equation or Numeric Field ,Number of decimal places)
sqlite> SELECT OnHandQuan 'Quantity',Descrip 'Description', ROUND(PackCost/PackQty,2) 'Unit Price',ROUND((PackCost/PackQty) * OnHandQuan,2) 'Ext' FROM inventory;
Quantity|Description|Unit Price|Ext
15|Shovel Pointed Long Handle|9.82|147.3
14|White Gas Gallon Can|3.69|51.63
36|10W-30 Motor Oil, Quart|1.52|54.6
17|5W-30 Motor Oil, Quart|1.52|25.78
92|AAA Dry Cells 4 Pack |0.75|69.0
173|AA Dry Cells 4 Pack |0.75|129.75
5|AA Dry Cells 8 Pack|1.4|7.0
19|D Dry Cells 8 Pack|7.52|142.82
92|Ball Point Pens Blue Fine tip, 12pack|0.77|70.7
sqlite>
The results look a little jumbled and are hard to read. The presentation of the query results can be improved by the following commands. In SQLite, query results can be produced in 8 different formats using the " .mode". The default is list mode in which each field is separated by a specified character. The default character is the pipe "|". This example uses the dot command ".mode column". The ".width" specifies the space allocated to each field, the default is 10 spaces per field.
.mode column
.width 10 30 10 10
sqlite>.mode column
sqlite>.width 10 30 10 10
sqlite> SELECT OnHandQuan 'Quantity',Descrip 'Description', ROUND(PackCost/PackQty,2) 'Unit Price',ROUND((PackCost/PackQty) * OnHandQuan,2) 'Ext' FROM inventory;
| Quantity | Description | Unit Price | Ext |
| ---------- | -------------------------- | ---------- | ---------- |
| 15 | Shovel Pointed Long Handle | 9.82 | 147.3 |
| 14 | White Gas Gallon Can | 3.69 | 51.63 |
| 36 | 10W-30 Motor Oil, Quart | 1.52 | 54.6 |
| 17 | 5W-30 Motor Oil, Quart | 1.52 | 25.78 |
| 92 | AAA Dry Cells 4 Pack | 0.75 | 69.0 |
| 173 | AA Dry Cells 4 Pack | 0.75 | 129.75 |
| 5 | AA Dry Cells 8 Pack | 1.4 | 7.0 |
| 19 | D Dry Cells 8 Pack | 7.52 | 142.82 |
| 92 | Ball Point Pens Blue Fine | 0.77 | 70.7 |
What if we want to know the total value of the inventory? We could of course add up the values in the Ext column but that could take awhile particularly if there were hundreds of different items in the inventory. A better way would be to use the SUM aggregate function to add all those values for us.
sqlite> SELECT SUM((PackCost/PackQty) * OnHandQuan) 'Total Value of Inventory' FROM inventory;
Total Value of Inventory
------------------------
698.577
sqlite>
SQLite does not have a currency format for output but we can simulate it by using pipes "||" to append a dollar sign to the result after it has been rounded to two decimal places.
sqlite> SELECT '$ ' || ROUND(SUM((PackCost/PackQty) * OnHandQuan),2) 'Total Value of Inventory' FROM inventory;
Total Value of Inventory
------------------------
$ 698.58
sqlite>
The Use of CAST for the Division of INTEGER Values
It should be mentioned that caution should be used when dividing one integer value with another in SQLite. In the example below we are dividing 5 by 3 which is about 1.6666666666667 however if SQLite sees two integer numbers it will round down to the nearest integer.
sqlite> SELECT 5/3;
1
sqlite>
A work around for this is to either add a decimal point to one of the numbers in the calculation or to use the CAST(number or column name AS REAL) function to change the type to a floating point number.
sqlite> SELECT 5.0/3;
1.66666666666667
sqlite> SELECT CAST(5 AS REAL)/3;
1.66666666666667
sqlite>
In the next demonstration we have a table called "tbl_int" with two columns. One is declared to have INTEGER values (MyInt) and the other REAL values(MyDec).
CREATE TABLE tbl_int(MyInt INTEGER,MyDec REAL);
INSERT INTO tbl_int VALUES(1,1.5);
INSERT INTO tbl_int VALUES(3,3.7);
INSERT INTO tbl_int VALUES(499,499);
INSERT INTO tbl_int VALUES(43,42.999);
INSERT INTO tbl_int VALUES(17,17);
If we execute a SELECT query in which the values in each column are divided by two and then multiplied by two we can see that the result of the "INT" column is one less compared to the original INTEGER value (MyInt).
sqlite> SELECT MyInt, (MyInt/2)*2 AS 'INT', MyDec, (MyDec/2)*2 AS 'DEC' FROM tbl_int;
| MyInt | INT | MyDec | DEC |
| ---------- | ---------- | ---------- | ---------- |
| 1 | 0 | 1.5 | 1.5 |
| 3 | 2 | 3.7 | 3.7 |
| 499 | 498 | 499.0 | 499.0 |
| 43 | 42 | 42.999 | 42.999 |
| 17 | 16 | 17.0 | 17.0 |
Colin Riley -- March 2009 --