SQL Server Functions- Numeric Functions

Following are the SQL SERVER Numeric Functions.

ABS

- The ABS function returns the absolute value of the expression specified.

Syntax

ABS(number)

Example

select ABS(-542) as Absolute_Of_Number

Output

Absolute_Of_Number
542

AVG

- The AVG function returns the average value of the number.

Syntax

AVG(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select AVG(Experience) as Avrage_Experience

Output

Avrage_Experience
4

CEILING

- The CEILING function returns the smallest integer value that is greater than equal to the number specified in the expression.

Syntax

CEILING(number)

Example

select CEILING(23.24) as Ceiling_Number

Output

Ceiling_Number
24

COUNT

- The COUNT function returns the number of records in that particular table for expression specified. .

Syntax

Count(number)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select COUNT(Experience) as CountExperience

Output

CountExperience
4

FLOOR

- The FLOOR function is the opposite of CEILING function. IT returns the largest integer value that is less than equal to the number specified in the expression.

Syntax

FLOOR(number)

Example

select FLOOR(23.24) as floor_Number

Output

floor_Number
23

MAX

- The MAX function is used to obtain the maximum value of expression.

Syntax

MAX(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select MAX(Experience) as Max_Experience

Output

Max_Experience
7

MIN

- The MIN function is used to obtain the minimum value of expression.

Syntax

MIN(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select MIN(Experience) as Max_Experience

Output

Max_Experience
2

RAND

- The RAND function returns the random decimal number greater than 0 and less than 1.

Syntax

RAND(seed)

Example

select RAND() as Random_Number

Output

Random_Number
0.849042974736912

Note- The RAND function returns the any random value. The seed is optional. If seed is provided, it will give repeatable sequence of Random number every time.

ROUND

- The ROUND function returns the number rounded to certain number of decimal places.

Syntax

ROUND(number_to_round,decimal_places,operation)

Parameters

number_to_round- number to be rounded.
decimal_places- number of decimal places upto which number to be rounded.
operation- This is optional parameter. It can be 0 or 1. When specified as 0, it will round the result to the number of decima_places. Or if any value other than 0 is specified. It will truncate the result to the number of decimal_places. By defalut , value is 0.

Example

select ROUND(232.25,1) as Rounded_Number

Output

Rounded_Number
232.30

Example_2

select ROUND(232.25,1,1) as Rounded_Number

Output

Rounded_Number
232.20

SIGN

- The SIGN function returns the value indicating sign of the number.

Syntax

SIGN (number)

Example

select SIGN(25) as Sign_Of_Number

Output

Sign_Of_Number
1

If the number provided is >0 then returns 1 If the number provided is =0 then returns 0 If the number provided is <0 then returns -1

Example_2

select SIGN(-25) as Sign_Of_Number

Output

Sign_Of_Number
-1

SUM

- The SUM function returns the sum of the given expression.

Syntax

SUM(expression)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Ankit Pandey	Bihar	2017-06-15 01:03:34.247	4
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	2
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

select SUM(Experience) as Sum_Number

Output

Sum_Number
16