Loading

MySQL Numeric or Mathematical Function Part2

In MySQL Numeric or Mathematical Function Part 2, we will discuss the AVG Function, CEIL Function, CEILING Function,  COS Function, COT Function, COUNT Function, and CUME_DIST.

AVG Function:- AVG Function is used for returning the average value of a given list of numeric values.

Syntax:- AVG(List of numeric value)

For Example:-

SELECT * FROM Employee WHERE age > (SELECT AVG(age) FROM Employee );

CEIL Function:- CEIL Function is used for returning the smallest integer value which will be equal to or greater than a given number.

Syntax:- CEIL(number)

For Example:-

SELECT CEIL(20.56);

Output:- 21

SELECT CEIL(-20.56);

Output:- -20

CEILING Function:- CEILING Function is similar to CEIL

Syntax:- CEILING(number)

COS Function:- COS Function is used for giving the cosine of a given number. Where the number will indicate radians.

Syntax:-  COS(number)

For Example:-

SELECT COS(3);

Output:- -0.9899924966004454

SELECT COS(-3);

Output:- -0.9899924966004454

SELECT COS(1.5);

Output:- 0.0707372016677029

COT Function:- COT Function is used for returning the cotangent value of any given number.

Syntax:- COT(number)

For Example:-

SELECT COT(3);

Output:- -7.015252551434534

SELECT COT(-3);

Output:- 7.015252551434534

SELECT COT(1.5);

Output:- 0.07091484430265245

COUNT Function:- COUNT Function is used for returning the total count of expression. It counts either all rows or defined some rows of the table. Its return type is BIGINT. If nothing will count it will return zero. It will not count NULL value.

Syntax:-SELECT  COUNT(expression) or COUNT(*) or COUNT(distinct) FROM table name WHERE conition

For Example:-

SELECT COUNT(deparment_name) FROM school;

CUME_DIST Function:- CUME_DIST function returns the cumulative distribution value among a set of the given rows. CUME_DIST Function is used for returning the cumulative distribution of a given row. It is a window function.

This function uses the following formula:-

ROW_NUMBER() / total_rows

Syntax:- CUME_DIST() OVER (PARTITION BY expr, …ORDER BY expr [ASC | DESC], …)

For Example:-

SELECT studentname, markes, ROW_NUMBER() OVER (ORDER BY markes) row_num, CUME_DIST() OVER (ORDER BY marks) cume_dist_val FROM result;

Read Also:-

MySQL Numeric or Mathematical Function Part1
MySQL Numeric or Mathematical Function Part3
MySQL Numeric or Mathematical Function Part4
MySQL Numeric or Mathematical Function Part5

Read more detail about MySQL Numeric or Mathematical Function manual

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w
Verified by ExactMetrics