Aggregate Functions
Wed, 03/04/2009 - 20:02 | 
hdanzl
Specify a value computed from a group of rows.
Syntax
|
| |
COUNT ( * ) |
|
<set function type> ( [ DISTINCT | ALL ] <value expression> ) |
|
| | | | | | | |
AVG MAX MIN SUM COUNT MED STD LIST |
Usage
The aggregate functions, also referred to as set functions, perform operations that aggregate data from groups of rows.
Notes
| ♦ | ALL is implicit if no set quantifier is not specified. |
| ♦ | If DISTINCT is specified, then all duplicate values are removed from the set. |
| ♦ | If the query specifies a GROUP BY clause, then the aggregate functions are applied to each group, else the entire table is treated as a single group. |
| ♦ | If a general set function is specified, other than the COUNT function, and the set is empty, then the result is null. |
| ♦ | If COUNT( * ) is specified, then the result is the cardinality of the set, else the COUNT function returns the number of non-null values in the set. |
| ♦ | The data type of the result is BIGINT. |
| ♦ | The MAX function returns the highest non-null value in the set. |
| ♦ | The data type of the result is the same type as the argument. |
| ♦ | The MIN function returns the lowest non-null value in the set. |
| ♦ | The data type of the result is the same type as the argument. |
| ♦ | The AVG function calculates the average of non-null values in the set. |
| ♦ | The argument shall be a numeric type. |
| ♦ | The data type of the result is the same type as the argument. |
| ♦ | The SUM function calculates the sum of non-null values in the set. |
| ♦ | The argument shall be a numeric type. |
| ♦ | If the argument is an integer type, then the data type of the result is BIGINT. |
| ♦ | If the argument is a BCD type, then the data type of the result is DECIMAL with maximum possible precision and the same scale as the argument. |
| ♦ | If the argument is an approximate numeric type, then the data type of the result is DOUBLE PRECISION or EXTENDED. |
| ♦ | The MED function calculates the median value of the non-null values in the set. |
| ♦ | The argument shall be a numeric type. |
| ♦ | The data type of the result is the same type as the argument. |
| ♦ | The STD function calculates the standard deviation of the non-null values in the set. |
| ♦ | The argument shall be a numeric type. |
| ♦ | The data type of the result is the same type as the argument. |
| ♦ | LIST is a special NexusDB set function that compiles a comma-separated list of non-null values in the set. |
| ♦ | The argument shall be a character string type. |
| ♦ | The data type of the result is CHAR(4096). |
| ♦ | If DISTINCT is specified, then all duplicate values are removed from the set. |
| ♦ | If DISTINCT is specified, then the items in the list are sorted otherwise unsorted. |
Examples
| 1) | The following example calculates the number of rows in the students table: |
SELECT COUNT( * ) FROM students
| 2) | The following example returns the number of female students: |
SELECT COUNT( studentName )
FROM students
WHERE gender = 'F'
| 3) | The following example calculates the average grade for each student: |
SELECT studentID, AVG( grade )
FROM enrolls
GROUP BY studentID
| 4) | The following example selects the highest and lowest salaries of teachers: |
SELECT MAX( salary ), MIN( salary )
FROM teachers
| 5) | The following example calculates the total cost of each order made: |
SELECT orderID, SUM( extPrice )
FROM order_details
GROUP BY orderID
| 6) | The following example retrieves a list of student names grouped by gender: |
SELECT gender, LIST( studentName )
FROM students
GROUP BY gender
Conformance
|
SQL:2003 standard NexusDB extensions |
- - - - |
Core SQL MED function STD function LIST function |
Tags:
Questions? Problems?
- Interested to buy but something put you off?
- Can't find what you're looking for?
- Have a suggestion?
Let us know. If you're having trouble contacting us, please check here.
![[]](/support/modules/ubercart/uc_cart/images/bullet-arrow-up.gif "Expand cart block."){kind=icon}
| 1x | NexusDB AWE DLL (1 installation; binary) | AUD 330.00 |
| 1x | NexusDB Developer DCU Edition | AUD 600.00 |
| 1x | The Full Monty Pack | AUD 1,800.00 |
| 1x | NexusDB Professional Edition | AUD 1,400.00 |
| 1x | Memory Manager V4 | AUD 360.00 |
| 1x | NexusDB AWE DLL (site license; binary) | AUD 2,300.00 |
| 1x | NexusDB Embedded Source | AUD 430.00 |
| 1x | NexusDB PHP Connector | AUD 500.00 |
| 1x | NexusDB Server License Plugin | AUD 250.00 |
| 1x | NexusDB Starter Edition DCU | AUD 150.00 |
| 1x | NexusDB Developer Source Edition | AUD 890.00 |
| 11 Items | Total: AUD 9,010.00 |
| (View cart) (Checkout) | |
Site Updates
- Dec 4 2024 - 1:53pm
- Dec 4 2024 - 1:48pm
- Nov 19 2024 - 1:04pm
- Oct 17 2024 - 10:27am
- Jul 24 2024 - 1:22pm