SQLite | Query Data | Calculate Arithmetic Expressions from Column Values
When querying data, you can calculate values by applying arithmetic to one or more columns. This article explains how.
Arithmetic on Columns
| Operator | Description |
|---|---|
| a + b | Add b to a |
| a - b | Subtract b from a |
| a * b | Multiply a by b |
| a / b | Divide a by b |
| a % b | Return the remainder of a divided by b |
Use an operator in the SELECT list as follows.
SELECT column_name operator value FROM table_name;
select id, sale * 3 from report;
select id, price * count from report;
The first example returns three times each sale value. The second multiplies price by count for each row.
Create a table and insert sample data.
create table product (name text, num integer, price integer, discount integer);
insert into product values ('Mouse', 7, 2500, 0);
insert into product values ('NotePC', 2, 65000, 12000);
insert into product values ('Display', 4, 35000, 0);
insert into product values ('Printer', 5, 8000, 2000);
insert into product values ('Keyboard', 4, 10000, 0);
Subtract discount from price.
select *, price - discount from product;
name num price discount price - discount
Mouse 7 2500 0 2500
NotePC 2 65000 12000 53000
Display 4 35000 0 35000
Printer 5 8000 2000 6000
Keyboard 4 10000 0 10000
The expression becomes the result column’s default name. Use an AS clause to assign a clearer alias. For details, see Assign an Alias to a Result Column (AS Clause).
select *, price - discount as result from product;
name num price discount result
Mouse 7 2500 0 2500
NotePC 2 65000 12000 53000
Display 4 35000 0 35000
Printer 5 8000 2000 6000
Keyboard 4 10000 0 10000