Loading

MySQL Numeric or Mathematical Function Part2

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 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;

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w