# A Quick Look at SQL Server 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`

### Conclusion

SQL is quite powerful, and I hope this guide has helped you with your math problems in SQL