SQL Aggregate Functions

SQL built-in Aggregate functions

Summary

  • Aggregate functions perform a calculation on a set of values and return a single value.
  • There is a differentiated range of built-in aggregate functions in the various RDBMS, yet are there some functions which have the same name and executes the same.
  • There are also functions that perform the same but does not have the same name in different RDBMS.
  • Here is a collection of the most important built-in aggregate functions:
    Function RDBMS Description
    AVG() SQL server, MySQL, Oracle, PostgreSQL Aggregates the average of a values in a column.
    COUNT() SQL server, MySQL, Oracle, PostgreSQL Aggregates the number of values in a column or the number of rows in a table
    MAX() SQL server, MySQL, Oracle, PostgreSQL Aggregates the largest value in a column
    MIN() SQL server, MySQL, Oracle, PostgreSQL Aggregates the smallest value in a column
    STDDEV() MySQL, Oracle Aggregates the standard deviation of the values in a column.
    STDEVP() SQL server
    STDDEV_POP() PostgreSQL
    SUM() SQL server, MySQL, Oracle, PostgreSQL Aggregates the sum of the values in a column
    VAR_POP() MySQL, Oracle, PostgreSQL Aggregates the variance of the values in a column.
    VARP() SQL server

AVG() function

Aggregates the average of a values in a column.
Function syntax:
AVG  ( [ distinct | all ] <expression> )
/* all is the default */
AVG Example:
SQL server, MySQL, Oracle, PostgreSQL:
select avg(item_qty*PRICE) "Average Order sum"
    from order_books; 
The result should be:
Average Order sum
119.454545454545

COUNT() function

Aggregates the number of values in a column or the number of rows in a table
Function syntax:
COUNT  ( [ distinct | all ] <expression> )
/* all is the default */
Two types of count() function :
  1. COUNT can take an expression as an argument and discovers all non-null occurrences of that argument in a table.
  2. COUNT(*) has an asterisk as an argument and selects all rows, even if some columns contain a NULL value.
COUNT Example:
SQL server, MySQL, Oracle, PostgreSQL :
select count(telephone_number), count(*)
  from telephones
The result should be:
count(telephone_number) count(*)
8 10

MAX() function

Aggregates the largest value in a column
Function syntax:
MAX  ( [ distinct | all ] <expression> )
/* all is the default */
MAX Example:
SQL server, MySQL, Oracle, PostgreSQL :
select MAX(item_qty*PRICE) "Max. Order sum"
    from order_books; 
The result should be:
Max. Order sum
390

MIN() function

Aggregates the smallest value in a column
Function syntax:
MIN  ( [ distinct | all ] <expression> )
/* all is the default */
MIN Example:
SQL server, MySQL, Oracle, PostgreSQL :
select MIN(item_qty*PRICE) "Min. Order sum"
    from order_books; 
The result should be:
Min. Order sum
29

STDDEV() function

Aggregates the standard deviation of the values in a column in MySQL and Oracle.
MySQL, Oracle Function syntax:
STDDEV  ( [ distinct | all ] <expression> )
/* all is the default */
STDDEV Example:
MySQL, Oracle:
select STDDEV(item_qty*PRICE) "Standard dev. Order sum"
    from order_books; 
The result should be:
Standard dev. Order sum
101.14918202731

STDEVP() function

Aggregates the standard deviation of the values in a column in SQL server.
SQL server Function syntax:
STDEVP  ( [ distinct | all ] <expression> )
/* all is the default */
STDEVP Example:
SQL server:
select STDEVP(item_qty*PRICE) "Standard dev. Order sum"
    from order_books; 
The result should be:
Standard dev. Order sum
101.14918202731

STDDEV_POP() function

Aggregates the standard deviation of the values in a column in PostgreSQL.
PostgreSQL Function syntax:
STDDEV_POP  ( [ distinct | all ] <expression> )
/* all is the default */
STDDEV_POP Example:
PostgreSQL:
select STDDEV_POP(item_qty*PRICE) "Standard dev. Order sum"
    from order_books; 
The result should be:
Standard dev. Order sum
101.14918202731

SUM() function

Aggregates the sum of the values in a column
Function syntax:
SUM  ( [ distinct | all ] <expression> )
/* all is the default */
SUM Example:
SQL server, MySQL, Oracle, PostgreSQL :
select SUM(item_qty*PRICE) "Total Ordered"
    from order_books; 
The result should be:
Total Ordered
1314

VAR_POP() function

Aggregates the variance of the values in a column in MySQL, Oracle and PostgreSQL.
MySQL, Oracle, PostgreSQL Function syntax:
VAR_POP  ( [ distinct | all ] <expression> )
/* all is the default */
VAR_POP Example:
MySQL, Oracle, PostgreSQL:
select VAR_POP(PRICE) "Variance of Price"
    from order_books; 
The result should be:
Variance of Price
32.611570247934

VARP() function

Aggregates the variance of the values in a column in SQL server.
SQL server Function syntax:
VARP  ( [ distinct | all ] <expression> )
/* all is the default */
VARP Example:
SQL server:
select VARP(PRICE) "Variance of Price"
    from order_books; 
The result should be:
Variance of Price
32.611570247934
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.