<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Aggregate Functions</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Functions and Operators" HREF="functions.html"><LINK REL="PREVIOUS" TITLE="Array Functions and Operators" HREF="functions-array.html"><LINK REL="NEXT" TITLE="Subquery Expressions" HREF="functions-subquery.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions-array.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="functions.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 9. Functions and Operators</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="functions.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="functions-subquery.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="FUNCTIONS-AGGREGATE" >9.15. Aggregate Functions</A ></H1 ><A NAME="AEN12307" ></A ><P > <I CLASS="FIRSTTERM" >Aggregate functions</I > compute a single result value from a set of input values. <A HREF="functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE" >Table 9-37</A > shows the built-in aggregate functions. The special syntax considerations for aggregate functions are explained in <A HREF="sql-expressions.html#SYNTAX-AGGREGATES" >Section 4.2.7</A >. Consult <A HREF="tutorial-agg.html" >Section 2.7</A > for additional introductory information. </P ><DIV CLASS="TABLE" ><A NAME="FUNCTIONS-AGGREGATE-TABLE" ></A ><P ><B >Table 9-37. Aggregate Functions</B ></P ><TABLE BORDER="1" CLASS="CALSTABLE" ><COL><COL><COL><COL><THEAD ><TR ><TH >Function</TH ><TH >Argument Type</TH ><TH >Return Type</TH ><TH >Description</TH ></TR ></THEAD ><TBODY ><TR ><TD > <A NAME="AEN12327" ></A > <CODE CLASS="FUNCTION" >avg(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE > </TD ><TD > <TT CLASS="TYPE" >smallint</TT >, <TT CLASS="TYPE" >integer</TT >, <TT CLASS="TYPE" >bigint</TT >, <TT CLASS="TYPE" >real</TT >, <TT CLASS="TYPE" >double precision</TT >, <TT CLASS="TYPE" >numeric</TT >, or <TT CLASS="TYPE" >interval</TT > </TD ><TD > <TT CLASS="TYPE" >numeric</TT > for any integer type argument, <TT CLASS="TYPE" >double precision</TT > for a floating-point argument, otherwise the same as the argument data type </TD ><TD >the average (arithmetic mean) of all input values</TD ></TR ><TR ><TD > <A NAME="AEN12345" ></A > <CODE CLASS="FUNCTION" >bit_and(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE > </TD ><TD > <TT CLASS="TYPE" >smallint</TT >, <TT CLASS="TYPE" >integer</TT >, <TT CLASS="TYPE" >bigint</TT >, or <TT CLASS="TYPE" >bit</TT > </TD ><TD > same as argument data type </TD ><TD >the bitwise AND of all non-null input values, or null if none</TD ></TR ><TR ><TD > <A NAME="AEN12358" ></A > <CODE CLASS="FUNCTION" >bit_or(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE > </TD ><TD > <TT CLASS="TYPE" >smallint</TT >, <TT CLASS="TYPE" >integer</TT >, <TT CLASS="TYPE" >bigint</TT >, or <TT CLASS="TYPE" >bit</TT > </TD ><TD > same as argument data type </TD ><TD >the bitwise OR of all non-null input values, or null if none</TD ></TR ><TR ><TD > <A NAME="AEN12371" ></A > <CODE CLASS="FUNCTION" >bool_and(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE > </TD ><TD > <TT CLASS="TYPE" >bool</TT > </TD ><TD > <TT CLASS="TYPE" >bool</TT > </TD ><TD >true if all input values are true, otherwise false</TD ></TR ><TR ><TD > <A NAME="AEN12382" ></A > <CODE CLASS="FUNCTION" >bool_or(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE > </TD ><TD > <TT CLASS="TYPE" >bool</TT > </TD ><TD > <TT CLASS="TYPE" >bool</TT > </TD ><TD >true if at least one input value is true, otherwise false</TD ></TR ><TR ><TD ><CODE CLASS="FUNCTION" >count(*)</CODE ></TD ><TD > </TD ><TD ><TT CLASS="TYPE" >bigint</TT ></TD ><TD >number of input values</TD ></TR ><TR ><TD ><CODE CLASS="FUNCTION" >count(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE ></TD ><TD >any</TD ><TD ><TT CLASS="TYPE" >bigint</TT ></TD ><TD > number of input values for which the value of <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > is not null </TD ></TR ><TR ><TD > <A NAME="AEN12409" ></A > <CODE CLASS="FUNCTION" >every(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE > </TD ><TD > <TT CLASS="TYPE" >bool</TT > </TD ><TD > <TT CLASS="TYPE" >bool</TT > </TD ><TD >equivalent to <CODE CLASS="FUNCTION" >bool_and</CODE ></TD ></TR ><TR ><TD ><CODE CLASS="FUNCTION" >max(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE ></TD ><TD >any numeric, string, or date/time type</TD ><TD >same as argument type</TD ><TD > maximum value of <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > across all input values </TD ></TR ><TR ><TD ><CODE CLASS="FUNCTION" >min(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE ></TD ><TD >any numeric, string, or date/time type</TD ><TD >same as argument type</TD ><TD > minimum value of <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > across all input values </TD ></TR ><TR ><TD > <A NAME="AEN12437" ></A > <CODE CLASS="FUNCTION" >stddev(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE > </TD ><TD > <TT CLASS="TYPE" >smallint</TT >, <TT CLASS="TYPE" >integer</TT >, <TT CLASS="TYPE" >bigint</TT >, <TT CLASS="TYPE" >real</TT >, <TT CLASS="TYPE" >double precision</TT >, or <TT CLASS="TYPE" >numeric</TT > </TD ><TD > <TT CLASS="TYPE" >double precision</TT > for floating-point arguments, otherwise <TT CLASS="TYPE" >numeric</TT > </TD ><TD >sample standard deviation of the input values</TD ></TR ><TR ><TD ><CODE CLASS="FUNCTION" >sum(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >)</CODE ></TD ><TD > <TT CLASS="TYPE" >smallint</TT >, <TT CLASS="TYPE" >integer</TT >, <TT CLASS="TYPE" >bigint</TT >, <TT CLASS="TYPE" >real</TT >, <TT CLASS="TYPE" >double precision</TT >, <TT CLASS="TYPE" >numeric</TT >, or <TT CLASS="TYPE" >interval</TT > </TD ><TD > <TT CLASS="TYPE" >bigint</TT > for <TT CLASS="TYPE" >smallint</TT > or <TT CLASS="TYPE" >integer</TT > arguments, <TT CLASS="TYPE" >numeric</TT > for <TT CLASS="TYPE" >bigint</TT > arguments, <TT CLASS="TYPE" >double precision</TT > for floating-point arguments, otherwise the same as the argument data type </TD ><TD >sum of <TT CLASS="REPLACEABLE" ><I >expression</I ></TT > across all input values</TD ></TR ><TR ><TD > <A NAME="AEN12475" ></A > <CODE CLASS="FUNCTION" >variance</CODE >(<TT CLASS="REPLACEABLE" ><I >expression</I ></TT >) </TD ><TD > <TT CLASS="TYPE" >smallint</TT >, <TT CLASS="TYPE" >integer</TT >, <TT CLASS="TYPE" >bigint</TT >, <TT CLASS="TYPE" >real</TT >, <TT CLASS="TYPE" >double precision</TT >, or <TT CLASS="TYPE" >numeric</TT > </TD ><TD > <TT CLASS="TYPE" >double precision</TT > for floating-point arguments, otherwise <TT CLASS="TYPE" >numeric</TT > </TD ><TD >sample variance of the input values (square of the sample standard deviation)</TD ></TR ></TBODY ></TABLE ></DIV ><P > It should be noted that except for <CODE CLASS="FUNCTION" >count</CODE >, these functions return a null value when no rows are selected. In particular, <CODE CLASS="FUNCTION" >sum</CODE > of no rows returns null, not zero as one might expect. The <CODE CLASS="FUNCTION" >coalesce</CODE > function may be used to substitute zero for null when necessary. </P ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><A NAME="AEN12495" ></A ><A NAME="AEN12497" ></A ><P ><B >Note: </B > Boolean aggregates <CODE CLASS="FUNCTION" >bool_and</CODE > and <CODE CLASS="FUNCTION" >bool_or</CODE > correspond to standard SQL aggregates <CODE CLASS="FUNCTION" >every</CODE > and <CODE CLASS="FUNCTION" >any</CODE > or <CODE CLASS="FUNCTION" >some</CODE >. As for <CODE CLASS="FUNCTION" >any</CODE > and <CODE CLASS="FUNCTION" >some</CODE >, it seems that there is an ambiguity built into the standard syntax: </P><PRE CLASS="PROGRAMLISTING" >SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;</PRE ><P> Here <CODE CLASS="FUNCTION" >ANY</CODE > can be considered both as leading to a subquery or as an aggregate if the select expression returns 1 row. Thus the standard name cannot be given to these aggregates. </P ></BLOCKQUOTE ></DIV ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > Users accustomed to working with other SQL database management systems may be surprised by the performance characteristics of certain aggregate functions in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > when the aggregate is applied to the entire table (in other words, no <TT CLASS="LITERAL" >WHERE</TT > clause is specified). In particular, a query like </P><PRE CLASS="PROGRAMLISTING" >SELECT min(col) FROM sometable;</PRE ><P> will be executed by <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > using a sequential scan of the entire table. Other database systems may optimize queries of this form to use an index on the column, if one is available. Similarly, the aggregate functions <CODE CLASS="FUNCTION" >max()</CODE > and <CODE CLASS="FUNCTION" >count()</CODE > always require a sequential scan if applied to the entire table in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN >. </P ><P > <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > cannot easily implement this optimization because it also allows for user-defined aggregate queries. Since <CODE CLASS="FUNCTION" >min()</CODE >, <CODE CLASS="FUNCTION" >max()</CODE >, and <CODE CLASS="FUNCTION" >count()</CODE > are defined using a generic API for aggregate functions, there is no provision for special-casing the execution of these functions under certain circumstances. </P ><P > Fortunately, there is a simple workaround for <CODE CLASS="FUNCTION" >min()</CODE > and <CODE CLASS="FUNCTION" >max()</CODE >. The query shown below is equivalent to the query above, except that it can take advantage of a B-tree index if there is one present on the column in question. </P><PRE CLASS="PROGRAMLISTING" >SELECT col FROM sometable ORDER BY col ASC LIMIT 1;</PRE ><P> A similar query (obtained by substituting <TT CLASS="LITERAL" >DESC</TT > for <TT CLASS="LITERAL" >ASC</TT > in the query above) can be used in the place of <CODE CLASS="FUNCTION" >max()</CODE >. </P ><P > Unfortunately, there is no similarly trivial query that can be used to improve the performance of <CODE CLASS="FUNCTION" >count()</CODE > when applied to the entire table. </P ></BLOCKQUOTE ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="functions-array.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="functions-subquery.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Array Functions and Operators</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="functions.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Subquery Expressions</TD ></TR ></TABLE ></DIV ></BODY ></HTML >