Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Aggregate Functions

An aggregate function is a special kind of function that operates on several rows of a query at once, returning a single result. Such functions are generally only used in queries which make use of the GROUP BY clause to associate rows together by like criteria, though they may be used in queries which only contain aggregate functions in their target list. When performing the latter, the aggregate function operates on all selected rows from the result set.

Table 5-16 provides an overview of PostgreSQL's supported aggregate functions. To see a complete list of aggregate functions, you may use the \da command within psql .

Table 5-16. Aggregate functions

Function

Description

avg( expression  )

Returns the average of the expression values from all rows in a group.

count( expression  )

Returns the number of values, per each aggregated group of rows, for which expression is not NULL

max( expression  )

Returns the maximum value of expression in the grouped rows

min( expression  )

Returns the minimum value of expression in the grouped rows

stddev( expression  )

Returns the standard deviation of the values of expression in the grouped rows

sum( expression  )

Returns the sum of the values of expression in the grouped rows

variance( expression  )

Returns the variance of the values of expression in the grouped rows

The following sections describe each aggregate function in further detail, including specific information on usage, examples, and valid input data types. In each of the functional explanations, the term expression refers to any valid identifier in a result set, or any valid expression operating on such an identifier.

Aggregate expressions

When calling an aggregate function, aggregate expressions are employed to describe an expression from the result set created by the SELECT statement. An aggregate expression is similar to an ordinary SQL expression, but may be preceded by either the ALL or the DISTINCT keyword.

The use of the DISTINCT keyword in an aggregate expression causes only grouped rows with unique values (as described by the expression) to be evaluated by the function. Any duplicate rows will be suppressed. Similar to the use of the ALL keyword in a SELECT statement, the use of ALL in an aggregate expression has no function other than to make more explicit the request for all grouped rows to be evaluated to the function. Example 5-19 demonstrates each of the aggregate expression forms.

Example 5-19. Using aggregate expressions

booktown=# 
SELECT count(location) AS set_locations,

booktown-# 
       count(ALL location) AS all_set_locations,

booktown-# 
       count(DISTINCT location) AS unique_locations,

booktown-# 
       count(*) AS all_rows

booktown-# 
       FROM subjects;

 set_locations | all_set_locations | unique_locations | all_rows
---------------+-------------------+------------------+----------
            15 |                15 |                7 |       16
(1 row)

There is one final form of aggregate expression, as demonstrated by the all_rows result column in Example 5-19. When the asterisk (*) symbol is supplied as the aggregate expression, it instructs the aggregate function to evaluate all rows , including rows with values of NULL, which are ordinarily ignored. Since the subjects table contains one row with a NULL value in the location column, the counted rows for location differ from those counted for *.

Warning

Rows whose evaluated aggregate expression contain NULL values will not be evaluated by an aggregate function (with the exception of the count() function).

avg()

avg(
expression
 )

The avg() function accepts an expression describing aggregated values that are either of any numeric type (numeric, bigint, smallint, real, or double precision), or of the interval time type.

The average, or mean, of the values described by expression in the grouped rows is returned. The resultant value is returned as a value of type numeric for expressions of type integer and double precision for expressions of type real. All other expression types cause a value of the same data type to be returned.

Examples

booktown=# 
SELECT avg(cost) AS average_cost,

booktown-# 
       avg(retail) AS average_price,

booktown-# 
       avg(retail - cost) AS average_profit

booktown-# 
       FROM stock;

 average_cost  | average_price | average_profit
---------------+---------------+----------------
 24.8235294118 | 30.0088235294 |   5.1852941176
(1 row)

booktown=# 
SELECT avg(cost) AS average_cost, p.name AS publisher

booktown-# 
       FROM (stock JOIN editions USING (isbn))

booktown-# 
       JOIN publishers AS p (publisher_id) 

booktown-# 
                       USING (publisher_id)

booktown-# 
       GROUP BY p.name;

 average_cost  |          publisher
---------------+-----------------------------
 26.5000000000 | Ace Books
 19.0000000000 | Books of Wonder
 26.5000000000 | Doubleday
 25.0000000000 | HarperCollins
 18.0000000000 | Henry Holt & Company, Inc.
 23.0000000000 | Kids Can Press
 23.0000000000 | Mojo Press
 20.0000000000 | Penguin
 23.0000000000 | Random House
 26.5000000000 | Roc
 26.0000000000 | Watson-Guptill Publications
(11 rows)

count()

count(
expression
 )

The count() function returns the number of values in a set of aggregated rows where the expression is not NULL. The count() is not restricted as to the data type described by expression . It is important to understand that the count() function only counts values which are not NULL. As a result, it is important to use an expression whose value will not be returned NULL in order for the expression to be meaningful to the counted results.

You may pass the asterisk (*) character to count() in order to simply count all rows in an aggregation (including rows with NULL values).

Examples

booktown=# 
SELECT count(*) FROM editions;

 count
-------
    17
(1 row)

booktown=# 
SELECT count(isbn), p.name

booktown-# 
       FROM editions JOIN publishers AS p (publisher_id)

booktown-# 
                     USING (publisher_id)

booktown-# 
       GROUP BY p.name

booktown-# 
       ORDER BY count DESC;

 count |            name
-------+-----------------------------
     3 | Random House
     2 | Ace Books
     2 | Doubleday
     2 | Roc
     1 | Books of Wonder
     1 | HarperCollins
     1 | Henry Holt & Company, Inc.
     1 | Kids Can Press
     1 | Mojo Press
     1 | O'Reilly & Associates
     1 | Penguin
     1 | Watson-Guptill Publications
(12 rows)

max()

max(
expression
 )

The max() function returns the maximum found value described by expression in a set of aggregated rows. It accepts an expression that may represent any numeric, string, date, or time data type. The maximum is returned as a value of the same data type as the expression .

Examples

booktown=# 
SELECT max(cost), max(retail) FROM stock;

  max  |  max
-------+-------
 36.00 | 46.95
(1 row)

booktown=# 
SELECT max(retail), p.name

booktown-# 
       FROM (stock NATURAL JOIN editions)

booktown-# 
       JOIN publishers AS p (publisher_id)

booktown-# 
                       USING (publisher_id)

booktown-# 
       GROUP BY p.name

booktown-# 
       ORDER BY max DESC;

  max  |            name
-------+-----------------------------
 46.95 | Roc
 45.95 | Ace Books
 36.95 | Doubleday
 32.95 | Random House
 28.95 | HarperCollins
 28.95 | Watson-Guptill Publications
 24.95 | Mojo Press
 24.95 | Penguin
 23.95 | Henry Holt & Company, Inc.
 23.95 | Kids Can Press
 21.95 | Books of Wonder
(11 rows)

min()

min(
expression
 )

The min() function returns the minimum found value described by expression in a set of aggregated rows. It accepts an expression which may represent any numeric, string, date, or time data type. The minimum is returned as a value of the same data type as the expression .

Examples

booktown=# 
SELECT min(cost), min(retail) FROM stock;

  min  |  min
-------+-------
 16.00 | 16.95
(1 row)

booktown=# 
SELECT min(retail), p.name

booktown-# 
       FROM (stock NATURAL JOIN editions)

booktown-# 
       JOIN publishers AS p (publisher_id)

booktown-# 
                       USING (publisher_id)

booktown-# 
       GROUP BY p.name

booktown-# 
       ORDER BY min ASC;

  min  |            name
-------+-----------------------------
 16.95 | Random House
 21.95 | Ace Books
 21.95 | Books of Wonder
 22.95 | Roc
 23.95 | Henry Holt & Company, Inc.
 23.95 | Kids Can Press
 24.95 | Mojo Press
 24.95 | Penguin
 28.95 | Doubleday
 28.95 | HarperCollins
 28.95 | Watson-Guptill Publications
(11 rows)

stddev()

stddev(
expression
 )

The stddev() function accepts an expression describing values of any numeric type (numeric, bigint, smallint, real, or double precision), and returns the standard deviation of the values within the aggregated rows. The resultant value is returned as double precision for an expression describing floating point values, and numeric for all other types.

Examples

booktown=# 
SELECT stddev(retail) FROM stock;

 stddev
--------
   8.46
(1 row)

booktown=# 
SELECT stddev(retail), p.name

booktown-# 
       FROM (stock NATURAL JOIN editions)

booktown-# 
       JOIN publishers AS p ON (publisher_id = p.id)

booktown-# 
       GROUP BY p.name

booktown-# 
       ORDER BY stddev DESC

booktown-# 
       LIMIT 4;

 stddev |     name
--------+--------------
  16.97 | Ace Books
  16.97 | Roc
   8.02 | Random House
   5.66 | Doubleday
(4 rows)

sum()

sum(
expression
 )

The sum() function accepts an expression describing values of any numeric type (numeric, bigint, smallint, real, or double precision), and returns the sum of the values within the aggregated rows. The returned value is of the type numeric when operating on values of type integer and double precision when operating on values of type real. The result is returned as the same data type as the values described by expression for all other data types.

Examples

booktown=# 
SELECT sum(stock) FROM stock
;
 sum
-----
 508
(1 row)

booktown=# 
SELECT sum(stock), s.subject

booktown-# 
       FROM ((stock NATURAL JOIN editions)

booktown(# 
       JOIN books ON (books.id = book_id))

booktown-# 
       JOIN subjects AS s

booktown-# 
                     ON (books.subject_id = s.id)

booktown-# 
       GROUP BY s.subject

booktown-# 
       ORDER BY sum DESC;

 sum |     subject
-----+------------------
 189 | Horror
 166 | Science Fiction
  91 | Children's Books
  28 | Drama
  18 | Classics
  16 | Arts
(6 rows)

variance()

variance(
expression
 )

The variance() function accepts an expression describing values of any numeric type (numeric, bigint, smallint, real, or double precision) and returns the variance of the values within the aggregated rows. The variance is equivalent to the stddev() squared. The resultant value is returned as double precision for an expression describing floating-point values, and numeric for all other types.

Examples

booktown=# 
SELECT variance(retail) FROM stock;

 variance
----------
    71.60
(1 row)

booktown=# 
SELECT variance(retail), p.name

booktown-# 
       FROM (stock NATURAL JOIN editions)

booktown-# 
       JOIN publishers AS p

booktown-# 
                       ON (editions.publisher_id = p.id)

booktown-# 
       GROUP BY p.name

booktown-# 
       ORDER BY variance DESC

booktown-# 
       LIMIT 4;

 variance |            name
----------+-----------------------------
   288.00 | Ace Books
   288.00 | Roc
    64.33 | Random House
    32.00 | Doubleday
(4 rows)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire