**Topics Covered:**

**Functions – Introduction****MySQL Single Row Functions****MySQL Aggregate Functions****AVG()****COUNT()****MAX()****MIN()****SUM()**

.

#### Functions – Introduction

My SQL function performs a pre-defined task and returns a single value such as a numerical, string, or date/time value.

Functions operate on zero, one, two, or more values provided to them, these values are called parameters or arguments.

MySQL support number of built-in functions. To use any Function, we need to specify the column to which the function should be applied.

e.g. SELECT function(column name) FROM tablename;

#### Types of SQL Functions

SQL provides two types of functions, which are as blows :

**(i) Single-row Functions **This type of function work with a single row at a time. It returns a result for each row of the table, on which the query is performed. Examples of single-row functions include CHAR( ), CONCAT( ), INSTR( ), etc.

**(ii) Multiple-row Functions **This type of function work with data of multiple rows at a time and return a single output value. Examples of multiple-row functions include SUM(), AVG(), COUNT(), MAX(), MIN().

### Mathematical Functions

MySQL provides a number of functions used for performing mathematical calculations on the database. Mathematical functions are very important in SQL to implement different mathematical concepts in queries.

Mathematical functions are explained with examples as follows:

Function | Description | Example |

MOD ( A,B) | Return the remainder of A/B | SELECT MOD(11,3) ; OUTPUT:- 2 SELECT MOD(10.5,3) ; OUTPUT:- 1.5 |

POWER ( A,B) or POW(A,B) | Returns the value of A raised to the power B | SELECT POWER(5,3) ; OUTPUT:- 125 SELECT POW(2,-2) ; OUTPUT:- 0.25 SELECT POWER(-2,3) ; OUTPUT:- -8 SELECT POWER(2.37,3.45) ; OUTPUT:- 19.6282…. |

ROUND (N,D ) | Return number rounded to D place after decimal, If D is not specified,N is rounded up to 0 digit of decimal and the result is an Integer. If the first digit after the decimal value is 5 or >5 then integer number is increased by 1 If D is +ve ,then N is rounded up to D digits after the decimal by checking D+1th digit,if it is 5or <5,then the d^{th}digit is increased by 1 If D is -ve , then N is rounded up to D digits before the decimal by checking D^{th} digit, if it is 5 or >5 then d-1th digit is increased by 1,all trailing dth digits are converted to 0 and the result is an integer value. | SELECT ROUND(1.58) ; OUTPUT:- 2 SELECT ROUND(-1.23) ; OUTPUT:- -1 SELECT ROUND(1.298,0) ; OUTPUT:- 1 SELECT ROUND(-5.898,0) ;OUTPUT:- -6 SELECT ROUND(3.79867,3) ; OUTPUT:- 3.799 SELECT ROUND(23.298,-1) ; OUTPUT:- 20 SELECT ROUND(36567.78,-4) ; OUTPUT:- 40000 |

SIGN ( ) | Return the sign of the given number | SELECT SIGN(-10) ; OUTPUT:- -1 SELECT SIGN(10) ; OUTPUT:- 1 |

SQRT ( ) | Return the non-negative square root of the given number | SELECT SQRT(25) ; OUTPUT:- 5 |

TRUNCATE( x, d) | Truncates x to the specified number of d. If d is 0,it removes all the decimal values and returns integer. If d is +ve, Truncates x to d digits right to the decimal point. If d is -ve, Truncates x to d digits left fo the decimal point. | SELECT TRUNCATE(7.29, 0) ; OUTPUT:- 7 SELECT TRUNCATE(27.59, 1) ; OUTPUT:- 27.5 SELECT TRUNCATE(389.23, -2) ; OUTPUT:- 300 |

ABS (value ) | Returns the absolute value of the given value. | SELECT ABS(-25) ; OUTPUT:- 25 SELECT ABS(-234.67) ; OUTPUT:- 234.67 |

__String/Text Functions__

__String/Text Functions__

The string/text functions of SQL are used to extract, change, format or alter character strings. They accept a character string as an input and provides character string or numeric values as an output.

String/text functions are explained with example as follows :

Function | Description | Example |

ASCII( ) | Returns the ASCII code of the character. | SELECT ASCII (‘A’ ) FROM DUAL; OUTPUT:- 65 SELECT ASCII (‘1’ ) FROM DUAL; OUTPUT:- 49 SELECT ASCII (‘ABC’ ) FROM DUAL; OUTPUT:- 65 |

TRIM ( ) | Remove spaces from beginning and ending | SELECT TRIM( ‘ APPLE ‘); OUTPUT:’APPLE’ |

LTRIM () | Returns string after removing left side of spaces. | SELECT LTRIM( ‘ APPLE ‘); OUTPUT:’APPLE ’ |

RTRIM () | Returns string after removing right side of spaces. | SELECT LTRIM( ‘ APPLE ‘); OUTPUT:’ APPLE’ |

INSTR( ) | It search one string in another string and returns position, if not found 0 | SELECT INSTR (‘COMPUTER’,’PUT’) ; OUTPUT: – 4 SELECT INSTR (‘COMPUTER’,’PY’) ; OUTPUT: – 0 |

LENGTH ( ) | Returns number of Characters in the string. | SELECT LENGTH (‘COMPUTER’) ; OUTPUT: – 8 |

LEFT (S,N) | Returns N characters of String S from beginning | SELECT LEFT(‘COMPUTER’,3) ; OUTPUT:- COM |

RIGHT(S,N) | Returns N characters of String S from ending | SELECT RIGHT(‘COMPUTER’,3) ; OUTPUT:- TER |

SUBSTR ( ) | Returns the substring as specified | SELECT SUBSTR(‘COMPUTER’,3,4) ; OUTPUT:- MPUT |

CONCATE () | Returns concatenated String | SELECT CONCAT(‘COM’,’PUTER’) ; OUTPUT:- COMPUTER |

LOWER() / LCASE() | Converts string into lowercase. | SELECT LOWER(‘COMPUTER’) ; OUTPUT:- computer |

UPPER() / UCASE() | Converts string into uppercase. | SELECT UPPER(‘COMPUTER’) ; OUTPUT:- COMPUTER |

REPLACE() | Replace all occurrences of the second string in the first string with the third string. | SELECT REPLACE (‘INFO COMPUTER’,’INFO’,’LATEST’) ; OUTPUT:- LATEST COMPUTER |

REVERSE() | Return reverse of the given string. | SELECT REVERSE(‘COMPUTER’) ; OUTPUT:- RETUPMOC |

REPEAT () | Returns a given string for a specified number of times. | SELECT REPEAT(‘COM’,3) ; OUTPUT:- COMCOMCOM |

__Date/Time Functions__

__Date/Time Functions__

MySQL stores date in date/time format, representing the century, month, year, day and hours. The date and time functions are used to perform operations on the date/time data stored in the database. The default date format is YYYY-MM-DD in MySQL.

Date/time functions are explained with examples as follows:

Function | Description | Example |

CURDATE( ) | Returns the current Date | SELECT CURDATE( ) ; OUTPUT:- 14/06/2021 |

DATE( ) | Returns the Date part only | SELECT DATE( ‘01/07/2020’) ; OUTPUT:- 01 |

MONTH ( ) | Returns the Month part only | SELECT MONTH (‘01/07/2020’ ) OUTPUT: – 07 |

YEAR ( ) | Returns the Year part only | SELECT YEAR (‘01/07/2020’ ) OUTPUT: – 2020 |

DAYNAME ( ) | Returns the name of the week day | SELECT DAYNAME (‘01/07/2020’ ) OUTPUT: – WEDNESDAY |

MONTHNAME () | Returns the name of the month. | SELECT MONTHNAME (‘01/07/2020’ ) OUTPUT: – JULY |

DAYOFMONTH( ) | Returns the day of the month(1-31) | SELECT DAYOFMONTH (‘01/07/2020’ ) OUTPUT: – 01 |

DAYOFWEEK ( ) | Returns the weekday index of the date. | SELECT DAYOFWEEK (‘01/07/2020’ ) OUTPUT: – 04 |

DAYOFYEAR ( ) | Returns the day of the year(1-366). | SELECT DAYOFYEAR (‘01/07/2020’ ) OUTPUT: – 183 |

NOW ( ) | Returns both current date & time at which the function execute. | SELECT NOW ( ); |

SYSDATE ( ) | Returns the current date & time. | SELECT NOW ( ) , SLEEP(2), NOW( ); OUTPUT: 01/07/2020 04:20:32 01/07/2020 04:20:32 SELECT SYSDATE( ), SLEEP ( 2 ), SYSDATE( ); OUTPUT: 01/07/2020 04:20:32 01/07/2020 04:20:34 |

#### Difference Between NOW() and SYSDATE()

NOW() function returns the date and time at which the function was executed even if we execute multiple NOW() functions with SELECT. whereas SYSDATE() will always return date and time at which each SYSDATE() function started execution.

**For example:**

mysql> Select now(), sleep(2), now();

Output: 2018-12-04 10:26:20, 0, 2018-12-04 10:26:20

mysql> Select sysdate(), sleep(2), sysdate();

Output: 2018-12-04 10:27:08, 0, 2018-12-04 10:27:10

## Aggregate Functions

Aggregate functions are also called multiple-row functions. These functions work on a set of records as a whole and return a single value for each column of the records on which the function is applied.

Aggregate Functions is used to perform calculation on group of rows and return the calculated summary like sum of salary , average of salary.

1. AVG() 2. COUNT() 3.MAX()

4 . MIN() 5. SUM( )

__AVG() Function: __

The AVG() function returns the average value of a numeric column.

Syntax: SELECT AVG(column_name) FROM table_name

SQL AVG() Example: We have the following “Orders” table:

O_Id | OrderDate | OrderPrice | Customer |

1 | 2018/11/12 | 1000 | Vivek Kumar |

2 | 2021/10/23 | 1600 | Nitesh Sharma |

3 | 2020/09/02 | 700 | Vivek Kumar |

4 | 2019/09/03 | 300 | Vivek Kumar |

5 | 2020/08/30 | 2000 | Samarjeet Gupta |

6 | 2020/10/04 | 100 | Nitesh Sharma |

Now we want to find the average value of the “OrderPrice” fields. We use the following SQL statement:

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

The result-set will look like this:

OrderAverage |

950 |

Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value.

We use the following SQL statement:

SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders) ;

The result-set will look like this:

Customer |

Vivek Kumar |

Nitesh Sharma |

Samarjeet Gupta |

**COUNT( ) Function:**

The COUNT() function returns the number of rows that matches a specified criterion.

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name;

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name COUNT(column_name) ;

Example

We have the following “Orders” table:

O_Id | OrderDate | OrderPrice | Customer |

1 | 2018/11/12 | 1000 | Vivek Kumar |

2 | 2021/10/23 | 1600 | Nitesh Sharma |

3 | 2020/09/02 | 700 | Vivek Kumar |

4 | 2019/09/03 | 300 | Vivek Kumar |

5 | 2020/08/30 | 2000 | Samarjeet Gupta |

6 | 2020/10/04 | 100 | Nitesh Sharma |

Now we want to count the number of orders from “Customer Nitesh Sharma”. We use the following SQL statement:

SELECT COUNT(Customer) AS CustomerNitesh Sharma FROM Orders WHERE Customer=’Nitesh Sharma’

The result of the SQL statement above will be 2, because the customer Nitesh Sharma has made 2 orders in total:

Customer Nitesh Sharma |

2 |

Example:

If we omit the WHERE clause, like this:

SELECT COUNT(*) AS NumberOfOrders FROM Orders;

The result-set will look like this:

NumberOfOrders |

6 |

It returns the total number of rows in the table.

**COUNT(DISTINCT column_name)**

Example:

Now we want to count the number of unique customers in the “Orders” table. We use the following SQL statement:

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

The result-set will look like this:

NumberOfCustomers |

3 |

which is the number of unique customers (Vivek Kumar, Nitesh Sharma, and Samarjeet Gupta) in the “Orders” table.

**The MAX() Function**

The MAX() function returns the largest value of the selected column.

Syntax:

SELECT MAX(column_name) FROM table_name;

Example:

We have the following “Orders” table:

O_Id | OrderDate | OrderPrice | Customer |

1 | 2018/11/12 | 1000 | Vivek Kumar |

2 | 2021/10/23 | 1600 | Nitesh Sharma |

3 | 2020/09/02 | 700 | Vivek Kumar |

4 | 2019/09/03 | 300 | Vivek Kumar |

5 | 2020/08/30 | 2000 | Samarjeet Gupta |

6 | 2020/10/04 | 100 | Nitesh Sharma |

Now we want to find the largest value of the “OrderPrice” column.

We use the following SQL statement:

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders;

The result-set will look like this:

LargestOrderPrice |

2000 |

**The MIN() Function**

The MIN() function returns the smallest value of the selected column.

Syntax:

SELECT MIN(column_name) FROM table_name ;

Example: We have the following “Orders” table:

O_Id | OrderDate | OrderPrice | Customer |

1 | 2018/11/12 | 1000 | Vivek Kumar |

2 | 2021/10/23 | 1600 | Nitesh Sharma |

3 | 2020/09/02 | 700 | Vivek Kumar |

4 | 2019/09/03 | 300 | Vivek Kumar |

5 | 2020/08/30 | 2000 | Samarjeet Gupta |

6 | 2020/10/04 | 100 | Nitesh Sharma |

Now we want to find the smallest value of the “OrderPrice” column.

We use the following SQL statement:

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders;

The result-set will look like this:

SmallestOrderPrice |

100 |

#### The SUM() Function

The SUM() function returns the total sum of a numeric column.

Syntax: SELECT SUM(column_name) FROM table_name

Example: We have the following “Orders” table

O_Id | OrderDate | OrderPrice | Customer |

1 | 2018/11/12 | 1000 | Vivek Kumar |

2 | 2021/10/23 | 1600 | Nitesh Sharma |

3 | 2020/09/02 | 700 | Vivek Kumar |

4 | 2019/09/03 | 300 | Vivek Kumar |

5 | 2020/08/30 | 2000 | Samarjeet Gupta |

6 | 2020/10/04 | 100 | Nitesh Sharma |

Now we want to find the sum of all “OrderPrice” fields”.

We use the following SQL statement:

The result-set will look like this:

OrderTotal |

5700 |

__The GROUP BY Statement__

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Syntax:

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

O_Id | OrderDate | OrderPrice | Customer |

1 | 2018/11/12 | 1000 | Vivek Kumar |

2 | 2021/10/23 | 1600 | Nitesh Sharma |

3 | 2020/09/02 | 700 | Vivek Kumar |

4 | 2019/09/03 | 300 | Vivek Kumar |

5 | 2020/08/30 | 2000 | Samarjeet Gupta |

6 | 2020/10/04 | 100 | Nitesh Sharma |

Now we want to find the total sum (total order) of each customer. We will have to use the GROUP BY statement to group the customers.

We use the following SQL statement:

SELECT Customer, SUM(OrderPrice) FROM Orders GROUP BY Customer;

The result-set will look like this:

Customer | SUM(OrderPrice) |

Vivek Kumar | 2000 |

Nitesh Sharma | 1700 |

Samarjeet Gupta | 2000 |

Let’s see what happens if we omit the GROUP BY statement:

SELECT Customer, SUM(OrderPrice) FROM Orders;

The result-set will look like this:

Customer | SUM(OrderPrice) |

Vivek Kumar | 5700 |

Nitesh Sharma | 5700 |

Vivek Kumar | 5700 |

Vivek Kumar | 5700 |

Samarjeet Gupta | 5700 |

Nitesh Sharma | 5700 |

The SELECT statement above has two columns specified (Customer and SUM(OrderPrice).

The “SUM(OrderPrice)” returns a single value (that is the total sum of the “OrderPrice” column), while “Customer” returns 6 values (one value for each row in the “Orders” table).

Due to this mismatch, the above query is not giving the correct output. To resolve this problem you need to use group by clause.

**GROUP BY More Than One Column**

We can also use the GROUP BY statement on more than one column, like this:

SELECT Customer, OrderDate, SUM(OrderPrice) FROM Orders GROUP BY Customer,OrderDate ;

#### The HAVING Clause

HAVING Clause in SQL is used to specify conditions on the rows with GROUP BY clause. The HAVING clause was added to MySQL because the WHERE keyword could not be used with aggregate functions.

Syntax:

SELECT column_name, aggregate_function(column_name) FROM table_name

WHERE column_name operator value GROUP BY column_name

HAVING aggregate_function(column_name) operator value;

Example: We have the following “Orders” table:

O_Id | OrderDate | OrderPrice | Customer |

1 | 2018/11/12 | 1000 | Vivek Kumar |

2 | 2021/10/23 | 1600 | Nitesh Sharma |

3 | 2020/09/02 | 700 | Vivek Kumar |

4 | 2019/09/03 | 300 | Vivek Kumar |

5 | 2020/08/30 | 2000 | Samarjeet Gupta |

6 | 2020/10/04 | 100 | Nitesh Sharma |

Now we want to find if any of the customers have a total order of less than 2000.

We use the following SQL statement:

SELECT Customer, SUM(OrderPrice) FROM Orders GROUP BY Customer

HAVING SUM(OrderPrice)<2000 ;

The result-set will look like this:

Customer | SUM(OrderPrice) |

Nitesh Sharma | 1700 |

Now we want to find if the customers “Vivek Kumar” or “Samarjeet Gupta” have a total order of more than 1500.

We add an ordinary WHERE clause to the SQL statement:

SELECT Customer, SUM(OrderPrice) FROM Orders WHERE Customer=’Vivek Kumar’ OR Customer=’Samarjeet Gupta’ GROUP BY Customer HAVING SUM(OrderPrice)>1500 ;

The result-set will look like this:

Customer | SUM(OrderPrice) |

Vivek Kumar | 2000 |

Samarjeet Gupta | 2000 |