begriffs open source - ai-pg/blob - full-docs/src/sgml/html/tutorial-agg.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>2.7. Aggregate Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="tutorial-join.html" title="2.6. Joins Between Tables" /><link rel="next" href="tutorial-update.html" title="2.8. Updates" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">2.7. Aggregate Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="tutorial-join.html" title="2.6. Joins Between Tables">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="tutorial-sql.html" title="Chapter 2. The SQL Language">Up</a></td><th width="60%" align="center">Chapter 2. The <acronym class="acronym">SQL</acronym> Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="tutorial-update.html" title="2.8. Updates">Next</a></td></tr></table><hr /></div><div class="sect1" id="TUTORIAL-AGG"><div class="titlepage"><div><div><h2 class="title" style="clear: both">2.7. Aggregate Functions <a href="#TUTORIAL-AGG" class="id_link">#</a></h2></div></div></div><a id="id-1.4.4.8.2" class="indexterm"></a><p>
   3     Like  most  other relational database products,
   4     <span class="productname">PostgreSQL</span> supports
   5     <em class="firstterm">aggregate functions</em>.
   6     An aggregate function computes a single result from multiple input rows.
   7     For example, there are aggregates to compute the
   8     <code class="function">count</code>, <code class="function">sum</code>,
   9     <code class="function">avg</code> (average), <code class="function">max</code> (maximum) and
  10     <code class="function">min</code> (minimum) over a set of rows.
  11    </p><p>
  12     As an example, we can find the highest low-temperature reading anywhere
  13     with:
  14
  15 </p><pre class="programlisting">
  16 SELECT max(temp_lo) FROM weather;
  17 </pre><p>
  18
  19 </p><pre class="screen">
  20  max
  21 -----
  22   46
  23 (1 row)
  24 </pre><p>
  25    </p><p>
  26     <a id="id-1.4.4.8.5.1" class="indexterm"></a>
  27
  28     If we wanted to know what city (or cities) that reading occurred in,
  29     we might try:
  30
  31 </p><pre class="programlisting">
  32 SELECT city FROM weather WHERE temp_lo = max(temp_lo);     <em class="lineannotation"><span class="lineannotation">-- WRONG</span></em>
  33 </pre><p>
  34
  35     but this will not work since the aggregate
  36     <code class="function">max</code> cannot be used in the
  37     <code class="literal">WHERE</code> clause.  (This restriction exists because
  38     the <code class="literal">WHERE</code> clause determines which rows will be
  39     included in the aggregate calculation; so obviously it has to be evaluated
  40     before aggregate functions are computed.)
  41     However, as is often the case
  42     the query can be restated to accomplish the desired result, here
  43     by using a <em class="firstterm">subquery</em>:
  44
  45 </p><pre class="programlisting">
  46 SELECT city FROM weather
  47     WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
  48 </pre><p>
  49
  50 </p><pre class="screen">
  51      city
  52 ---------------
  53  San Francisco
  54 (1 row)
  55 </pre><p>
  56
  57     This is OK because the subquery is an independent computation
  58     that computes its own aggregate separately from what is happening
  59     in the outer query.
  60    </p><p>
  61     <a id="id-1.4.4.8.6.1" class="indexterm"></a>
  62     <a id="id-1.4.4.8.6.2" class="indexterm"></a>
  63
  64     Aggregates are also very useful in combination with <code class="literal">GROUP
  65     BY</code> clauses.  For example, we can get the number of readings
  66     and the maximum low temperature observed in each city with:
  67
  68 </p><pre class="programlisting">
  69 SELECT city, count(*), max(temp_lo)
  70     FROM weather
  71     GROUP BY city;
  72 </pre><p>
  73
  74 </p><pre class="screen">
  75      city      | count | max
  76 ---------------+-------+-----
  77  Hayward       |     1 |  37
  78  San Francisco |     2 |  46
  79 (2 rows)
  80 </pre><p>
  81
  82     which gives us one output row per city.  Each aggregate result is
  83     computed over the table rows matching that city.
  84     We can filter these grouped
  85     rows using <code class="literal">HAVING</code>:
  86
  87 </p><pre class="programlisting">
  88 SELECT city, count(*), max(temp_lo)
  89     FROM weather
  90     GROUP BY city
  91     HAVING max(temp_lo) &lt; 40;
  92 </pre><p>
  93
  94 </p><pre class="screen">
  95   city   | count | max
  96 ---------+-------+-----
  97  Hayward |     1 |  37
  98 (1 row)
  99 </pre><p>
 100
 101     which gives us the same results for only the cities that have all
 102     <code class="structfield">temp_lo</code> values below 40.  Finally, if we only care about
 103     cities whose
 104     names begin with <span class="quote">“<span class="quote"><code class="literal">S</code></span>”</span>, we might do:
 105
 106 </p><pre class="programlisting">
 107 SELECT city, count(*), max(temp_lo)
 108     FROM weather
 109     WHERE city LIKE 'S%'            -- <span id="co.tutorial-agg-like"></span>(1)
 110     GROUP BY city;
 111 </pre><p>
 112
 113 </p><pre class="screen">
 114      city      | count | max
 115 ---------------+-------+-----
 116  San Francisco |     2 |  46
 117 (1 row)
 118 </pre><p>
 119    </p><div class="calloutlist"><table border="0" summary="Callout list"><tr><td width="5%" valign="top" align="left"><p><a href="#co.tutorial-agg-like">(1)</a> </p></td><td valign="top" align="left"><p>
 120       The <code class="literal">LIKE</code> operator does pattern matching and
 121       is explained in <a class="xref" href="functions-matching.html" title="9.7. Pattern Matching">Section 9.7</a>.
 122      </p></td></tr></table></div><p>
 123    </p><p>
 124     It is important to understand the interaction between aggregates and
 125     <acronym class="acronym">SQL</acronym>'s <code class="literal">WHERE</code> and <code class="literal">HAVING</code> clauses.
 126     The fundamental difference between <code class="literal">WHERE</code> and
 127     <code class="literal">HAVING</code> is this: <code class="literal">WHERE</code> selects
 128     input rows before groups and aggregates are computed (thus, it controls
 129     which rows go into the aggregate computation), whereas
 130     <code class="literal">HAVING</code> selects group rows after groups and
 131     aggregates are computed.  Thus, the
 132     <code class="literal">WHERE</code> clause must not contain aggregate functions;
 133     it makes no sense to try to use an aggregate to determine which rows
 134     will be inputs to the aggregates.  On the other hand, the
 135     <code class="literal">HAVING</code> clause always contains aggregate functions.
 136     (Strictly speaking, you are allowed to write a <code class="literal">HAVING</code>
 137     clause that doesn't use aggregates, but it's seldom useful. The same
 138     condition could be used more efficiently at the <code class="literal">WHERE</code>
 139     stage.)
 140    </p><p>
 141     In the previous example, we can apply the city name restriction in
 142     <code class="literal">WHERE</code>, since it needs no aggregate.  This is
 143     more efficient than adding the restriction to <code class="literal">HAVING</code>,
 144     because we avoid doing the grouping and aggregate calculations
 145     for all rows that fail the <code class="literal">WHERE</code> check.
 146    </p><p>
 147     Another way to select the rows that go into an aggregate
 148     computation is to use <code class="literal">FILTER</code>, which is a
 149     per-aggregate option:
 150
 151 </p><pre class="programlisting">
 152 SELECT city, count(*) FILTER (WHERE temp_lo &lt; 45), max(temp_lo)
 153     FROM weather
 154     GROUP BY city;
 155 </pre><p>
 156
 157 </p><pre class="screen">
 158      city      | count | max
 159 ---------------+-------+-----
 160  Hayward       |     1 |  37
 161  San Francisco |     1 |  46
 162 (2 rows)
 163 </pre><p>
 164
 165     <code class="literal">FILTER</code> is much like <code class="literal">WHERE</code>,
 166     except that it removes rows only from the input of the particular
 167     aggregate function that it is attached to.
 168     Here, the <code class="literal">count</code> aggregate counts only
 169     rows with <code class="literal">temp_lo</code> below 45; but the
 170     <code class="literal">max</code> aggregate is still applied to all rows,
 171     so it still finds the reading of 46.
 172    </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="tutorial-join.html" title="2.6. Joins Between Tables">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="tutorial-sql.html" title="Chapter 2. The SQL Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="tutorial-update.html" title="2.8. Updates">Next</a></td></tr><tr><td width="40%" align="left" valign="top">2.6. Joins Between Tables </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 2.8. Updates</td></tr></table></div></body></html>