Group Multiple Rows using GROUP BY

Inserting Data

**SQL(Structured Query Language) Tutorial**

SQL Functions

# SQL Functions

We had a small preview of SQL functions in the last page about grouping. We saw AVG() in action. Here we are going to see the other available functions. **Some of the functions may be same across different SQL databases - but many others will be different**. Here is a few links to the SQL function documentation for the most popular database servers...

- PostgreSQL - Find PostgreSQL's functions using \df command.
- MySQL Functions
- SQLite Core Functions
- Oracle
- SQL Functions
- General SQL Functions

Generally, functions are classified into two groups - aggregate functions and single value functions(or scalar functions).

By the way, all the functions given below are for MySQL. Most of them work in other Database servers too - but some don't. Refer to the manual of the server you are using to find the functions it supports. You are warned.

## Aggregate Functions

Aggregate functions are the **functions that needs multiple values** together to operate. It usually returns a single value. We saw this in the last page about grouping. In that example AVG() was an aggregate function. The most used aggregate functions are...

- AVG(column)
- Returns the average value of a column
- COUNT(column/*)
- Returns the number of rows
- MAX(column)
- Returns the highest value of a column
- MIN(column)
- Returns the lowest value of a column
- SUM(column)
- Adds all the values of the given column and returns the sum.

## Scalar Functions

These functions only **require one value as input**(instead of a whole columns or rows like in aggregate functions).

### String Functions

- CONCAT(str, str, str, ...)
- Return a concatenated string of all the values given as the argument.
- FORMAT(Number, Decimal Points)
- Return a number formatted to specified number of decimal places. Eg. FORMAT(15.3212, 2) returns 15.32
- TRIM(String)
- Remove leading and trailing spaces
- UPPER(String)
- Converts the string given as argument to upper case and returns it.
- LOWER(String)
- Converts to lower case and returns it.

### Numeric Functions

- CEIL(Decimal Number)
- Returns the decimal number rounded to the next integer. CEIL(5.2) returns 6.
- FLOOR(Decimal Number)
- Returns the decimal number rounded to the previous integer. CEIL(5.7) returns 5.
- ROUND(Number, Decimal Points)
- Rounds the given Number to the given Decimal points. If the second argument is not given, it returns the number rounded to the nearest integer.
- POW(Number,Power)
- Returns Number
^{Power}. - RAND()
- Returns a random floating-point value between 0 and 1.0.

### Date and Time Functions

In MySQL date are represented as 'YYYY-MM-DD' and time as 'YYYY-MM-DD HH:MM:SS'.

- ADDDATE(day, INTERVAL Number DAY)
- You can find the future using this function. At least, you can find the date of the future. ADDDATE('2005-03-30', INTERVAL 2 DAY) Returns '2005-04-01'
- DATEDIFF(day1, day2)
- Finds the difference between two days.
- CURDATE()
- Returns today's date.
- DATE_FORMAT(Date, Format)
- Formats the given date in the specified format.
- NOW()
- Returns the current time.
- YEAR(Date)
- Fetches and returns the year in the specified date
- MONTH(Date)
- Returns the month in the given date
- DAY(Date)
- Returns the day of the date given as the argument.

### Other Functions

- MD5(String)
- Returns the MD5 hash of the string provided as the argument.
- SHA1(String)
- Returns the SHA1 hash of the string