 # A Quick Look at SQL Server Numeric Functions

Thursday Dec 19th 2019 by Hannes Du Preez If you are wanting to manipulate numeric data with mathematically, then you will wan tot read on to get an overview of the core SQL numeric functions

The SQL language has a plethora of numeric and mathematic functions, and in this article, you’ll see how to make use of them.

The following is a list of the most popular SQL Numeric functions:

• ABS
• ACOS
• ASIN
• ATAN
• AVG
• CEILING
• COUNT
• COS
• COT
• DEGREES
• FLOOR
• MAX
• MIN
• POWER
• RAND
• ROUND
• SIN
• SQRT
• SUM
• TAN

Let’s have a look at them one-by-one

### ABS

The SQL ABS function returns the absolute value of a number. An Absolute value means how far a certain number is from zero. For example: -5 is 5 away from 0, and 5 is also 5 away from 0. Here is a short SQL example demonstrating the use of the ABS SQL function

```SELECT
ABS(-179.3) AS Abs1,
ABS(179.3) AS Abs2```

Both give the same result of 179.3, as the negative sign gets removed.

### ACOS

The ACOS SQL function returns the inverse cosine of a number. The next example shows how to obtain the arc cosine of a certain number:

`SELECT ACOS(0.17)`

`1.39996665766579`

### ASIN

The ASIN SQL function returns the inverse sine of a number. The next example shows how to obtain the arc sine of a certain number:

`SELECT SIN(0.17)`

`0.170829669129105`

### ATAN

The ATAN SQL function returns the inverse tangent of a number. The next example shows how to obtain the arc tangent of a certain number:

`SELECT TAN(17)`

`1.51204050407917`

### AVG

The AVG SQL function returns the average of an expression. The next example selects all the students whose average marks are greater than 75:

```SELECT
StudentName,
StudentSurname,
StudentMarks
FROM Students
WHERE AVG(StudentMarks) > 75
GROUP BY
StudentName,
StudentSurname,
StudentMarks
```

The AVG function is an aggregate function (a function that performs a calculation on one or more values, but returns a single value)

### CEILING

The CEILING SQL function returns the smallest value(integer) that is greater than or equal to a given number. The next example shows 57, because 57 is the next smallest integer value that is higher than 56.21:

`SELECT CEILING(56.21)`

### COUNT

The COUNT SQL function is also an aggregate function. It returns the number of records returned by a query. The next example will count the number of students that are doing a “Programming” class:

```SELECT
COUNT(StudentID)
FROM Students
WHERE StudentCourse = 'Programming'
```

### COS

The COS SQL function returns the cosine of a number. The next example shows how to obtain the cosine of a certain number:

`SELECT COS(0.17)`

`0.985584766909561`

### COT

The COT SQL function returns the cotangent of a number. The following shows how to obtain the cotangent of a certain number:

`SELECT COT(0.17)`

`5.82557679536221`

### DEGREES

The DEGREES SQL function converts radian values into degrees. The next example divides PI by 2 to return the 90 degrees:

`SELECT DEGREES(PI() / 2)`

### FLOOR

The FLOOR SQL function returns the largest value(integer) that is smaller than or equal to a given number. The next example shows 56, because 56 is the next biggest integer value that is smaller than 56.21:

`SELECT FLOOR(56.21)`

### MAX

The MAX SQL function is also an aggregate function. It returns the maximum value in a group of values. The next example will show each student's highest marks:

```SELECT
StudentName,
StudentSurname,
MAX(StudentMarks)
FROM Students
GROUP BY
StudentName,
StudentSurname,
StudentMarks```

### MIN

The MIN SQL function is also an aggregate function. It returns the minimum value in a group of values. The next example will show each student's lowest marks:

```SELECT
StudentName,
StudentSurname,
MIN(StudentMarks)
FROM Students
GROUP BY
StudentName,
StudentSurname,
StudentMarks```

### POWER

The POWER SQL function returns the value of one number raised to the power of another number. The next example shows the result of 11 x 11 x 11 (11 raised to the power of 3), which is 1331:

`SELECT POWER(11, 3)`

The RADIANS SQL function converts degree values into radians. The next example gets the radian value of 135 degrees:

`SELECT RADIANS(135)`

### RAND

The RAND SQL function produces a random number between zero and one. Here is a quick example:

`SELECT RAND()`

The above gives me 0.529394917183986 the first time, and 0.156402098552622 the second time

### ROUND

The ROUND SQL function rounds numeric values. The next example rounds the value of the sum of StudentMarks field to 2 decimal places where the course is ‘SQL’ and the student's name is ‘Hannes’:

```SELECT
StudentName,
StudentCourse,
ROUND(SUM(StudentMarks), 2)
FROM Students
WHERE StudentCourse = 'SQL' AND StudentName = 'Hannes'
GROUP BY
StudentName,
StudentCourse```

### SIN

The SIN SQL function returns the sine of a number. The next example shows how to obtain the sine of a certain number:

`SELECT SIN(0.17)`

`0.169182349066996`

### SQRT

The SQRT SQL function returns the square root of a number. The next example will return 7, because 7 * 7 equals 49:

`SELECT SQRT(49)`

### SUM

The SUM SQL function is also an aggregate function. It sums values of records returned by a query. The next example sums all the marks for a student named Hannes that is doing an SQL class:

```SELECT
StudentName,
StudentCourse,
SUM(StudentMarks)
FROM Students
WHERE StudentCourse = 'SQL' AND StudentName = 'Hannes'
GROUP BY
StudentName,
StudentCourse```

### TAN

The TAN SQL function returns the tangent of a number. The next example shows how to obtain the tangent of a certain number:

`SELECT TAN(17)`

`3.49391564547484`