SQLite provides various mathematical functions out of the box, and one of them is the power() function (also available as pow()).

The power() function calculates the result of raising one number to the power of another. In other words, it performs an exponentiation operation. This function is particularly useful for mathematical operations where powers or exponents are required.

Basic Syntax

The basic syntax of the power function is straightforward:

power(base, exponent)
-- or
pow(base, exponent)

Where:

• base: The number you want to raise to a power
• exponent: The power to which you want to raise the base

Example 1

Let’s say you want to calculate 4⁵ (4 raised to the power of 5). You can use the power() function as follows:

SELECT power(4, 5);

Result:

+-------------+
| power(4, 5) |
+-------------+
| 1024.0      |
+-------------+

In this example, 4 is raised to the power of 5, resulting in 1024. In other words, 4 x 4 x 4 x 4 x 4 = 1024.

Example 2

Let’s create a sample table of products to demonstrate how power() can be used with data in a database:

-- Create the products table
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    weight_kg DECIMAL(5,2)
);

-- Insert some sample data
INSERT INTO products (product_name, price, weight_kg) VALUES
    ('Basic Laptop', 499.99, 1.5),
    ('Premium Laptop', 1299.99, 2.1),
    ('Desktop Computer', 899.99, 8.5),
    ('Wireless Mouse', 24.99, 0.1),
    ('Gaming Monitor', 299.99, 4.2),
    ('Mechanical Keyboard', 129.99, 1.0);

Now let’s run some queries that use the power() function.

Here’s one that calculates the price squared for all products:

SELECT 
    product_name,
    price,
    round(power(price, 2), 2) as price_squared
FROM products;

Result:

+---------------------+---------+---------------+
|    product_name     |  price  | price_squared |
+---------------------+---------+---------------+
| Basic Laptop        | 499.99  | 249990.0      |
| Premium Laptop      | 1299.99 | 1689974.0     |
| Desktop Computer    | 899.99  | 809982.0      |
| Wireless Mouse      | 24.99   | 624.5         |
| Gaming Monitor      | 299.99  | 89994.0       |
| Mechanical Keyboard | 129.99  | 16897.4       |
+---------------------+---------+---------------+

Here’s one that returns all products where the price squared is greater than 100,000:

SELECT 
    product_name,
    price,
    round(power(price, 2), 2) as price_squared
FROM products
WHERE power(price, 2) > 100000;

Result:

+------------------+---------+---------------+
|   product_name   |  price  | price_squared |
+------------------+---------+---------------+
| Basic Laptop     | 499.99  | 249990.0      |
| Premium Laptop   | 1299.99 | 1689974.0     |
| Desktop Computer | 899.99  | 809982.0      |
+------------------+---------+---------------+

And finally, here’s one that calculates the price per kg cubed:

SELECT 
    product_name,
    price,
    weight_kg,
    round(price / power(weight_kg, 3), 2) as price_per_cubic_kg
FROM products
WHERE weight_kg > 0;

Result:

+---------------------+---------+-----------+--------------------+
|    product_name     |  price  | weight_kg | price_per_cubic_kg |
+---------------------+---------+-----------+--------------------+
| Basic Laptop        | 499.99  | 1.5       | 148.15             |
| Premium Laptop      | 1299.99 | 2.1       | 140.37             |
| Desktop Computer    | 899.99  | 8.5       | 1.47               |
| Wireless Mouse      | 24.99   | 0.1       | 24990.0            |
| Gaming Monitor      | 299.99  | 4.2       | 4.05               |
| Mechanical Keyboard | 129.99  | 1         | 129.99             |
+---------------------+---------+-----------+--------------------+

Rounding the Result

In some of the previous examples I used the ROUND() function to round the results to two decimal places. If I hadn’t done this, the fractional component would have been a lot larger on some rows.

Example:

SELECT 
    product_name,
    price,
    weight_kg,
    price / power(weight_kg, 3) as price_per_cubic_kg
FROM products
WHERE weight_kg > 0;

Result:

+---------------------+---------+-----------+--------------------+
|    product_name     |  price  | weight_kg | price_per_cubic_kg |
+---------------------+---------+-----------+--------------------+
| Basic Laptop        | 499.99  | 1.5       | 148.145185185185   |
| Premium Laptop      | 1299.99 | 2.1       | 140.372529964367   |
| Desktop Computer    | 899.99  | 8.5       | 1.46548341135762   |
| Wireless Mouse      | 24.99   | 0.1       | 24990.0            |
| Gaming Monitor      | 299.99  | 4.2       | 4.04910376849152   |
| Mechanical Keyboard | 129.99  | 1         | 129.99             |
+---------------------+---------+-----------+--------------------+

Using a Negative Base

Using a negative base with an integer exponent works fine, but using a negative base with a fractional exponent returns NULL:

SELECT 
       power(-4, 2),
       power(-4, 0.5);

Result:

+--------------+----------------+
| power(-4, 2) | power(-4, 0.5) |
+--------------+----------------+
| 16.0         | null           |
+--------------+----------------+

Things to Remember

• Both parameters must be numeric
• If either parameter is NULL, the result will be NULL
• The function returns a floating-point number
• Negative exponents are supported
• If you need integer results, you may want to use ROUND() or CAST()
• You can alternatively use pow() to get the same results as power()