SQL POWER Function

The SQL POWER() function is a Mathematical Function is used to calculate the power for the specified expression or numerical value It calculates the base value raised to the power of a given exponent.

The syntax of the POWER() function is shown below.

SELECT POWER (Float_Expression, Value)
FROM [Source]

Float_expression (base): It is the base value of the float data type. We can pass any value that can be implicitly converted to a float.

Value(exponent): It is the exponent. The POWER() function returns float_expression raised to the power of this argument.

SQL POWER Function Example

This math Function calculates the power of a given numeric value. The following query shows multiple ways to use it.

Use POWER() to calculate the square of a number

The query below uses POWER() to calculate the square of 10.

SELECT POWER(10, 2) AS Res
Res
100

Use POWER() to calculate the cube of a number

First, we declared a float variable, and we used it to calculate the power of the variable @i. It means, @i * @i * @i = 2 * 2 * 2 = 8.

In the next SQL Server statement, we used 4 as the second argument. It means 2 * 2 * 2 * 2 = 16. Please refer to the list of SQL Mathematical functions from our SQL tutorial page.

We used the integer value as the SQL Server POWER function input within the first two SQL SELECT statements. It means, (@j, 3) = @j * @j * @j => 2.20 * 2.20 * 2.20 = 10.65. But we are getting 8 as a result because it is rounding the 2.20 value to 2

DECLARE @i float = 2, @j int = 2.20

SELECT POWER(@i, 3) AS [SQLPowers]

SELECT POWER(@i, 4) AS [SQLPowers]

-- Calculating directly
SELECT POWER(3.20, 3) AS [SQLPowers]

-- Wrong Value
SELECT POWER(@j, 3) AS [SQLPowers]
SELECT POWER(2.20, 3) AS [SQLPower]
POWER FUNCTION 1

NOTE: Use float variables as this Math POWER Function input. Otherwise, you may expect strange results.

Using the POWER() function with a fractional exponent

The query below uses the base value as an integer and the exponent as a fractional value (float).

TIP: If the base value is an integer, the POWER() function returns an integer. If the base is a float, it returns a floating-point value.

SELECT POWER(10, 1.2) AS Res
Res
15

In this example query, we use the base value as a fraction and the exponent value as a fractional point.

SELECT POWER(10.0, 1.2) AS Res
Res
15.8

Using the POWER() function with zero exponent

Any value raised to the power of 0 becomes 1. This query uses base 10 and an exponent value of 0.

SELECT POWER(10, 0) AS Res
Res
1

Using the POWER() function with a negative exponent

The base value may be an integer or float; if we pass a negative value as the second argument (exponent), the POWER() function returns 0 as the result.

SELECT POWER(10, -2) AS r1, 
	POWER(10.5, -3) AS r2,
	POWER(10.5, -3.5) AS r3
r1	r2	r3
0	0.0	0.0

Use SQL POWER function on table columns

This function also allows you to calculate the power of column values. In this math function example, we are going to calculate the power of three for all records present in [Sales Amount]. For this demo, we use the data in the table below.

TIP: Please refer to the SQL SQRT, SQL SQUARE, and SQL ABS functions from the available Mathematical Functions.

SELECT [EnglishProductName]
      ,[Color]
      ,[StandardCost]
      ,[SalesAmount]
      ,POWER([SalesAmount], 3) AS Sales
 FROM [Mathemetical Functions]
POWER FUNCTION Example 2
Categories SQL