SQL Math Functions: 15 Definitions & Examples

In the world of SQL, math functions play a significant role in performing advanced calculations and analyzing complex data. They can be a key component to your data strategy that will help you better manipulate and manage your company's data.

That's why we've compiled this list of essential SQL math functions and accompanying examples for each one. Mastering these functions allows you to perform complex calculations and extract powerful insights from your database.

Let's get started.

SQL Math Functions

The SQL functions below are based on the following table:

Database: Beverages

1. Sum Function: SUM()

The sum function returns the total of all values in a column. 

Sum Function Example

SELECTSum(inventory)
FROMbeverages;

Output:

28

/*In this example, the sum function calculates the total of all values in the “Inventory” column.

2. Average Function: AVG()

The average function returns the average of all values in a column. 

Average Function Example

SELECTAverage(inventory)
FROMbeverages;

Output:

5.6

/*In this example, the output is 5.6 because that’s the average of all values in the “Inventory” column.

3. Round Function: ROUND()

The round function rounds a number to a specified number of decimal places. In the following syntax, the first number represents the number you want to round, while the second represents the number of decimal places you want to round to.

Round Function Example

SELECTRound(330.3333,1)

Output:

330.3

/*In this example, 330.3333 is rounded to the first decimal point. 

4. Truncate Function: TRUNCATE()

Like the round function, the truncate function also rounds a number to the specified number of decimal places.

Truncate Function Example

SELECTTruncate(330.3333,1)

Output:

330.3

/*In this example, 330.3333 is rounded to the first decimal point. 

5. Max Function: MAX()

The max function returns the largest number or value in a given column.

Max Function Example 

SELECTMax(price)
FROMbeverages;

Output:

400

/* The output for this example is 400 because lemonade is the highest price at 400$ per pallet. 

6. Min Function: MIN()

The min function returns the smallest number or value in a given column. 

Min Function Example

SELECTMin(price)
FROMbeverages;

Output:

12

/* The output for this example is 12 because milk is the lowest price at 12$ per crate. 

7. Count Function: COUNT()

The count function returns the number of rows that match the given criteria.

Count Function Example

SELECTCount(inventory)
FROMbeverages

Output:

5

/* The output for this example is five because that’s how many rows there are for the “Inventory” column in the “Beverages” table. 

8. Power Function: POWER()

The power function takes the first specified number and raises it to the power of the second number.

Power Function Example

SELECTPower(10,2);

Output:

100

/* In this example, 10 is raised to the power of 2, which equals 100. 

9. Log Function: LOG()

The log function returns the natural logarithmic value of a given number. You can also specify the base by adding an additional number, like in the example below.

Log Function Example

SELECTLog(5,2)

Output:

2.3219280948873622

/* In this example, we returned the logarithmic value of 5 to the base of 2.

10. Absolute Function: ABS()

The absolute function returns the absolute value of a given number.

Absolute Function Example

SELECTAbs(-8)

Output:

8

/* This example calculates the absolute value of -8, which is 8. 

11. Square Root Function: SQRT()

The square root function calculates the square root of a given number.

Square Root Function Example

SELECTSqrt(100);

Output:

10

/* This example calculates the square root of 100, which is 10. 

12. Sine Function: SIN()

The sine function returns the sine of a given number.

Sine Function Example

SELECTSin(100);

Output:

-0.50636564110975879

/*This example returns the sine of 100

13. Cosine Function: COS()

The cosine function calculates the cosine of a given number.

Cosine Function Example

SELECTCos(100);

Output:

0.86231887228768389

/*This example returns the cosine of 100

14. Modulo Function: MOD()

The modulo or mod function calculates the remainder of a number divided by another number.

Modulo Function Example

SELECTMod(8,3);

Output:

2

/*For this example, the output would be 2 because 8 divided by 3 creates a remainder of 2.

15. Ceil Function: CEIL()

The ceil or ceiling function rounds a given number up to the nearest integer.

Ceil Function Example

SELECTCeil(91.8);

Output:

92

/* This example rounds the number 91.8 to the nearest integer, which is 92.

Note: The ceil function can be used with MySQL. For more MySQL functions, click here.

By utilizing these functions in your SQL queries, you can manipulate data quickly and accurately and perform complex calculations that save valuable time and effort. From basic addition and subtraction to more complex operations like logarithms, we hope these math functions provide a helpful starting point for anyone seeking to work more efficiently with relational databases in SQL.

Author's Note:This post was written/edited by a human with the assistance of generative AI.