Group Multiple Rows using GROUP BY
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
- 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 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.
These functions only require one value as input(instead of a whole columns or rows like in aggregate 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.
- 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'.
- 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.
- 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.
- Returns the MD5 hash of the string provided as the argument.
- Returns the SHA1 hash of the string