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

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

Returns the average value of a column
Returns the number of rows
Returns the highest value of a column
Returns the lowest value of a 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
Remove leading and trailing spaces
Converts the string given as argument to upper case and returns it.
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.
Returns NumberPower.
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'.

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.
Returns today's date.
DATE_FORMAT(Date, Format)
Formats the given date in the specified format.
Returns the current time.
Fetches and returns the year in the specified date
Returns the month in the given date
Returns the day of the date given as the argument.

Other Functions

Returns the MD5 hash of the string provided as the argument.
Returns the SHA1 hash of the string
Group Multiple Rows using GROUP BY 
Inserting Data 
blog comments powered by Disqus
Subscribe to Feed