# Single-row and aggregate Oracle and MySQL functions
<a name="chap-oracle-aurora-mysql.sql.aggregate"></a>

Single-row and aggregate functions are essential SQL constructs that perform operations on individual rows or groups of rows, respectively. The following sections compare Oracle and MySQL single-row and aggregate functions.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-mysql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-mysql-migration-playbook/images/pb-automation-4.png)   |  N/A  |  MySQL doesn’t support all functions. These unsupported functions require manual creation.  | 

## Oracle usage
<a name="chap-oracle-aurora-mysql.sql.aggregate.oracle"></a>

Oracle provides two main categories of built-in SQL functions based on the number of rows used as input and generated as output.
+  **Single-row** or scalar functions return a single result for each row of the queried table or view. You can use them with a `SELECT` statement in the `WHERE` clause, the `START WITH` clause, the `CONNECT BY` clause, and the `HAVING` clause. The single-row functions are divided into groups according to data types such as `NUMERIC` functions, `CHAR` functions, and `DATETIME` functions.
+  **Aggregative** or group functions are used to summarize a group of values into a single result. Examples include `AVG`, `MIN`, `MAX`, `SUM`, `COUNT`, `LISTAGG`, `FIRST`, and `LAST`.

See the following section for a comparison of Oracle and MySQL single-row functions.

Oracle 19 adds ability to eliminate duplicate items in `LISTAGG` function results with new `DISTINCT` keyword.

Oracle 19 introduces several new bitmap SQL aggregate functions such as `BITMAP_BUCKET_NUMBER`, `BITMAP_BIT_POSITION` and `BITMAP_CONSTRUCT_AGG`. These functions help speed up `COUNT DISTINCT` operations.

For more information, see [Single-Row Functions](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Single-Row-Functions.html#GUID-B93F789D-B486-49FF-B0CD-0C6181C5D85C) and [Aggregate Functions](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Aggregate-Functions.html#GUID-62BE676BAF18-4E63-BD14-25206FEA0848) in *Oracle documentation*.

## MySQL usage
<a name="chap-oracle-aurora-mysql.sql.aggregate.mysql"></a>

MySQL provides an extensive list of single-row and aggregation functions. Some are similar to their Oracle counterparts by name and functionality, or under a different name but with similar functionality. Other functions can have identical names to their Oracle counterparts, but exhibit different functionality. In the following tables, the Equivalent column indicates functional equivalency.

### Numeric functions
<a name="chap-oracle-aurora-mysql.sql.aggregate.mysql.numeric"></a>


| Oracle function and definition | MySQL function and definition | Equivalent | 
| --- | --- | --- | 
|   `ABS` — Absolute value of n: `abs (-11.3) = 11.3`.  |   `ABS` — Absolute value of n: `abs (-11.3) = 11.3`.  |  Yes  | 
|   `CEIL` — Returns the smallest integer that is greater than or equal to n: `ceil (-24.9) = -24`.  |   `CEIL` — Returns the smallest integer that is greater than or equal to n: `ceil (-24.9) = -24`.  |  Yes  | 
|   `FLOOR` — Returns the largest integer equal to or less than n: `floor (-43.7) = -44`.  |   `FLOOR` — Returns the largest integer equal to or less than n: `floor (-43.7) = -44`.  |  Yes  | 
|   `MOD` — Remainder of n2 divided by n1: `mod(10,3) = 1`.  |   `MOD` — Remainder of n2 divided by n1: `mod(10,3) = 1`.  |  Yes  | 
|   `ROUND` — Returns n rounded to integer places to the right of the decimal point: `round (3.49, 1) = 3.5`.  |   `ROUND` — Returns n rounded to integer places to the right of the decimal point: `round (3.49, 1) = 3.5`.  |  Yes  | 
|   `TRUNC` — Returns n1 truncated to n2 decimal places: `trunc(13.5) = 13`.  |   `TRUNCATE` — Returns n1 truncated to n2 decimal places: `trunc(13.5) = 13`.  |  Yes  | 

### Character functions
<a name="chap-oracle-aurora-mysql.sql.aggregate.mysql.character"></a>


| Oracle function and definition | MySQL function and definition | Equivalent | 
| --- | --- | --- | 
|   `CONCAT` — Returns char1 concatenated with char2: `concat('a', 1) → a1`.  |   `CONCAT` — Returns char1 concatenated with char2: `concat('a', 1) → a1`.  |  Yes  | 
|   `LOWER` and `UPPER` — Returns char, with all letters lowercase or uppercase: `lower ('MR. Smith') → mr. smith`.  |   `LOWER` and `UPPER` — Returns char, with all letters lowercase or uppercase: `lower ('MR. Smith') → mr. smith`.  |  Yes  | 
|   `LPAD` and `RPAD` — Returns `expr1`, left or right padded to length n characters with the sequence of characters in `expr2`: `LPAD('Log-1',10,'-') → -----Log-1`.  |   `LPAD` and `RPAD` — Returns `expr1`, left or right padded to length n characters with the sequence of characters in `expr2`: `LPAD('Log-1',10,'-') → -----Log-1`.  |  Yes  | 
|   `REGEXP_REPLACE` — Search a string for a regular expression pattern: `regexp_replace('John', '[hn].', '1') → Jo1`.  |  You can simulate Oracle `REGEXP_REPLACE` function using MySQL built-in function.  |  No  | 
|   `REGEXP_SUBSTR` — Extends the functionality of the `SUBSTR` function by searching a string for a regular expression pattern: <pre>REGEXP_SUBSTR('http://www.aws.-com/products',<br />'http://([[:alnum:]]+\.?){3,4}/?')</pre>  `→ http://www.aws.com/`.  |  You can simulate Oracle `REGEXP_SUBSTR` function using MySQL built-in function.  |  No  | 
|   `REPLACE` — Returns char with every occurrence of search string replaced with a replacement string: `replace ('abcdef', 'abc', '123') → 123def`.  |   `REPLACE` — Returns char with every occurrence of search string replaced with a replacement string: `replace ('abcdef', 'abc', '123') → 123def`.  |  Yes  | 
|   `LTRIM` and `RTRIM` — Removes from the left or right end of char all of the characters that appear in set: `ltrim ('zzzyaws', 'xyz') → aws`.  |   `LTRIM` and `RTRIM` — Removes spaces from the left or right end of char: `ltrim(' Amazon') → Amazon`. Combine with the `REPLACE` function to get the results similar to Oracle.  |  Partly  | 
|   `SUBSTR` — Returns a portion of char, beginning at character position, substring length characters long: `substr('John Smith', 6 ,1) → S`.  |   `SUBSTR` — Returns a portion of char, beginning at character position, substring length characters long: `substr('John Smith', 6 ,1) → S`.  |  Yes  | 
|   `TRIM` — Trim leading or trailing characters or both from a character string: `trim (both 'x' FROM 'xJohnxx') → John`.  |   `TRIM` — Trim leading or trailing characters or both from a character string: `trim (both 'x' FROM 'xJohnxx') → John`.  |  Yes  | 
|   `ASCII` — Returns the decimal representation in the database character set of the first character of char: `ascii('a') → 97`.  |   `ASCII` — Returns the decimal representation in the database character set of the first character of char: `ascii('a') → 97`.  |  Yes  | 
|   `INSTR` — Search string for substring.  |   `INSTR` — Search string for substring.  |  Yes  | 
|   `LENGTH` — Returns the length of char: `length ('John S.') → 7`.  |   `LENGTH` — Returns the length of char: `length ('John S.') → 7`.  |  Yes  | 
|   `REGEXP_COUNT` — Returns the number of times, a pattern occurs in a source string.  |  You can simulate Oracle `REGEXP_COUNT` function using MySQL built-in function.  |  No  | 
|   `REGEXP_INSTR` — Searches a string position for a regular expression pattern.  |  You can simulate Oracle `REGEXP_INSTR` function using MySQL built-in function.  |  No  | 

### Date and time functions
<a name="chap-oracle-aurora-mysql.sql.aggregate.mysql.dt"></a>


| Oracle function and definition | MySQL function and definition | Equivalent | 
| --- | --- | --- | 
|   `ADD_MONTHS` — Returns the date plus integer months: `add_months( sysdate,1)`   |   `ADDDATE` — MySQL can implement the same functionality using the `ADDDATE` function.  |  No  | 
|   `CURRENT_DATE` — Returns the current date in the session time zone: `select current_date from dual → 2017-01-01 13:01:01`.  |   `CURRENT_DATE` — Returns date without time. Use the `now()` or the `current_timestamp` function to achieve the same results: `select now() → 2017-01-01 13:01:01`.  |  Partly  | 
|   `CURRENT_TIMESTAMP` — Returns the current date and time in the session time zone: `select current timestamp from dual; → 2017-01-01 13:01:01`.  |   `CURRENT_TIMESTAMP`— Returns the current date and time in the session time zone: `select current timestamp from dual; → 2017-01-01 13:01:01`.  |  Yes  | 
|   `EXTRACT (date part)` — Returns the value of a specified date time field from a date time or interval expression: `EXTRACT (YEAR FROM DATE '2017-03-07') → 2017`.  |   `EXTRACT (date part)` — Returns the value of a specified date time field from a date time or interval expression: `EXTRACT (YEAR FROM DATE '2017-03-07') → 2017`.  |  Yes  | 
|   `LAST_DAY` — Returns the date of the last day of the month that contains date: `LAST_DAY('05-07-2018') → 05-31-2018`.  |   `LAST_DAY` — Returns the date of the last day of the month that contains date: `LAST_DAY('05-07-2018') → 05-31-2018`.  |  Yes  | 
|   `BETWEEN` — Returns the number of months between dates date1 and date2: `MONTHS_BETWEEN ( sysdate, sysdate-100) → 3.25`.  |   `PERIOD_DIFF` — Returns the number of months between periods P1 and P2. P1 and P2 should be in the format `YYMM` or `YYYYMM`: `SELECT PERIOD_DIFF(201801,201703) → 10`   |  Partly  | 
|   `SYSDATE` — Returns the current date and time set for the operating system on which the database server resides: `select sysdate from dual → 2017-01-01 13:01:01`.  |   `SYSDATE` — Returns the current date and time set for the operating system on which the database server resides: `select sysdate() → 2017-01-01 13:01:01`.  |  Yes  | 
|   `SYSTIMESTAMP` — Returns the system date, including fractional seconds and time zone: `select systimestamp from dual → 2017-01-01 13:01:01.123456 PM+00:00`.  |   `CURRENT_TIMESTAMP` — Returns the current date and time in the session time zone: `select current timestamp from dual; → 2017-01-0113:01:01.123456+00`.  |  Yes  | 
|   `LOCALTIMESTAMP` — Returns the current date and time in the session time zone in a value of the `TIMESTAMP` data type: `select localtimestamp from dual → 01-JAN-17 10.01.10.123456 PM`.  |   `LOCALTIMESTAMP` — Returns the current date and time in the session time zone in a value of the `TIMESTAMP` data type: `select localtimestamp from dual → 01-JAN-17 10.01.10.123456 PM`.  |  Yes  | 
|   `TO_CHAR(datetime)` — Converts a date time or timestamp to data type to a value of `VARCHAR2` data type in the format specified by the date format: `to_char(sys-date, 'DD-MON-YYYY HH24:MI:SS') → 01-JAN-2017 01:01:01`.  |   `DATE_FORMAT` — Changes the format of the date and time: `DATE_FORMAT (SYSDATE(), '%Y-%m-%d %H:%i:%s')`   |  Yes  | 
|   `TRUNC (date)` — Returns a date with the time portion of the day truncated to the unit specified by the format model: `trunc(systimestamp) → 2017-01-01 00:00:00`.  |  You can simulate Oracle `TRUNC` function using MySQL built-in function.  |  No  | 

### Encoding and decoding functions
<a name="chap-oracle-aurora-mysql.sql.aggregate.mysql.encoding"></a>


| Oracle function and definition | MySQL function and definition | Equivalent | 
| --- | --- | --- | 
|   `DECODE` — Compares an expression to each search value one by one using the functionality of an `IF-THEN-ELSE` statement.  |   `CASE` — Compares an expression to each search value one by one.  |  No  | 
|   `DUMP` — Returns a `VARCHAR2` value containing the data type code, length in bytes, and internal representation of expression.  |  N/A  |  No  | 
|   `ORA_HASH` — Computes a hash value for a given expression.  |   `SHA` — Calculates an SHA-1 160-bit checksum for the string.  |  No  | 

### Null functions
<a name="chap-oracle-aurora-mysql.sql.aggregate.mysql.null"></a>


| Oracle function and definition | MySQL function and definition | Equivalent | 
| --- | --- | --- | 
|   `CASE` — Chooses from a sequence of conditions and runs a corresponding statement: `CASE WHEN condition THEN result [WHEN …​] [ELSE result] END`.  |   `CASE` — Chooses from a sequence of conditions and runs a corresponding statement: `CASE WHEN condition THEN result [WHEN …​] [ELSE result] END`.  |  Yes  | 
|   `COALESCE` — Returns the first non-null expr in the expression list: `coalesce (null, 'a', 'b') → a`.  |   `COALESCE` — Returns the first of its arguments that isn’t null: `coalesce (null, 'a', 'b') → a`.  |  Yes  | 
|   `NULLIF` — Compares `expr1` and `expr2`. If they are equal, the function returns null. If they aren’t equal, the function returns `expr1`: `NULLIF('a', 'b') → a`.  |   `NULLIF` — Compares `expr1` and `expr2`. If they are equal, the function returns null. If they aren’t equal, the function returns `expr1`: `NULLIF('a', 'b') → a`.  |  Yes  | 
|   `NVL` — Replaces null (returned as a blank) with a string in the results of a query: `NVL (null, 'a') → a`.  |   `IFNULL` — Replaces null (returned as a blank) with a string in the results of a query: `IFNULL (null, 'a') → a`.  |  No  | 
|   `NVL2` — Determines the value returned by a query based on whether a specified expression is null or not null.  |   `CASE` — Chooses from a sequence of conditions and runs a corresponding statement: `CASE WHEN condition THEN result [WHEN …​] [ELSE result] END`.  |  No  | 

### Environment and identifier functions
<a name="chap-oracle-aurora-mysql.sql.aggregate.mysql.environment"></a>


| Oracle function and definition | MySQL function and definition | Equivalent | 
| --- | --- | --- | 
|   `SYS_GUID` — Generates and returns a globally unique identifier (RAW value) made up of 16 bytes: `select sys_guid() from dual → 5A280ABA8C76201EE0530-100007FF691`.  |   `UUID` and `REPLACE` — `REPLACE(UUID(), '-', '')`.  |  No  | 
|   `UID` — Returns an integer that uniquely identifies the session user (the user who logged on): `select uid from dual → 84`.  |  N/A  |  No  | 
|   `USER` — Returns the name of the session user: `select user from dual`.  |   `USER` — Returns the name of the session user and source machine: `select USER()`.  |  No  | 
|   `USERENV` — Returns information about the current session using parameters: `SELECT USERENV ('LANGUAGE') "Language" FROM DUAL`.  |   `SHOW SESSION VARIABLES` — Displays the system variable values that are in effect for the current connection: `myshow SESSION VARIABLES LIKE 'collation_connection';`.  |  No  | 

### Oracle conversion functions
<a name="chap-oracle-aurora-mysql.sql.aggregate.mysql.conversion"></a>


| Oracle function and definition | MySQL function and definition | Equivalent | 
| --- | --- | --- | 
|   `CAST` — Converts one built-in data type or collection-typed value into another built-in data type or collection-typed value: `cast ('10' as int) + 1 → 11`.  |   `CAST` — Converts one built-in data type or collection-typed value into another built-in data type or collection-typed value: `cast ('10' as UNSIGNED) + 1`.  |  Yes  | 
|   `CONVERT` — Converts a character string from a one-character set to another: `select convert ('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') from dual`.  |   `CONVERT` — Converts a character string from a one-character set to another: `select convert ('Ä Ê Í Õ Ø A B C D E ' USING utf8)`.  |  Yes  | 
|   `TO_CHAR (string / numeric)` — Converts `NCHAR`, `NVARCHAR2`, `CLOB`, or `NCLOB` data to the database character set: `select to_char ('01234') from dual → 01234`.  |   `FORMAT` — Converts string data to the database character set: `FORMAT('01234', 0) -→ 01234`.  |  No  | 
|   `TO_DATE` — Converts char of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of `DATE` data type: `to_date('01Jan2017','DDMonYYYY') → 01-JAN-17`.  |   `STR_TO_DATE` — Convert string data type to a value of `DATE` data type: `SELECT STR_TO_DATE('01Jan2017','%d%M%Y')`.  |  No  | 
|   `TO_NUMBER` — Converts an expression to a value of `NUMBER` data type: `to_number('01234') → 1234 or to_number('01234', '99999') → 1234`.  |  N/A  |  No  | 

### Aggregate functions
<a name="chap-oracle-aurora-mysql.sql.aggregate.mysql.aggregate"></a>


| Oracle function and definition | MySQL function and definition | Equivalent | 
| --- | --- | --- | 
|   `AVG` — Returns an average value of an expression: `select avg(salary) from employees`.  |   `AVG` — Returns an average value of an expression: `select avg(salary) from employees`.  |  Yes  | 
|   `COUNT` — Returns the number of rows returned by the query: `select count(*) from employees`.  |   `COUNT` — Returns the number of rows returned by the query: `select count(*) from employees`.  |  Yes  | 
|   `LISTAGG` — Orders data within each group specified in the `ORDER BY` clause and then concatenates the values of the measure column: `select listagg(firstname,' ,') within group (order by customerid) from customer`.  |   `GROUP_CONCAT` — Orders data within each group specified in the `ORDER BY` clause and then concatenates the values of the measure column: `select GROUP_CONCAT(firstname order by customerid) from customer`.  |  No  | 
|   `MAX` — Returns the maximum value of an expression: `select max(salary) from employees`.  |   `MAX` — Returns the maximum value of an expression: `select max(salary) from employees`.  |  Yes  | 
|   `MIN` — Returns the minimum value of an expression: `select min(salary) from employees`.  |   `MIN` — Returns the minimum value of an expression: `select min(salary) from employees`.  |  Yes  | 
|   `SUM` — Returns the sum of values of an expression: `select sum(salary) from employees`.  |   `SUM` — Returns the sum of values of an expression: `select sum(salary) from employees`.  |  Yes  | 

### Top-N Query Oracle 12c
<a name="chap-oracle-aurora-mysql.sql.aggregate.mysql.topn"></a>


| Oracle function and definition | MySQL function and definition | Equivalent | 
| --- | --- | --- | 
|   `FETCH` — Retrieves rows of data from the result set of a multi-row query: `select * from customer fetch first 10 rows only`.  |   `LIMIT` — Retrieves just a portion of the rows that are generated by the rest of the query: `select * from customer LIMIT 10`.  |  Yes  | 

For more information, see [String Functions and Operators](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html) and [Numeric Functions and Operators](https://dev.mysql.com/doc/refman/5.7/en/numeric-functions.html) in the *MySQL documentation*.