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
• RADIANS
• 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)

This gives the answer of

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)

This gives the answer of

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)

This gives the answer of:

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)

This gives the answer of:

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)

This gives the answer of:

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)

RADIANS

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)

This gives the answer of:

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)

This gives the answer of:

3.49391564547484

Conclusion

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