diff options
| -rw-r--r-- | doc/src/sgml/query.sgml | 65 |
1 files changed, 49 insertions, 16 deletions
diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml index 9046d7c9fbe..a864d146f02 100644 --- a/doc/src/sgml/query.sgml +++ b/doc/src/sgml/query.sgml @@ -706,40 +706,39 @@ SELECT city FROM weather <indexterm><primary>HAVING</primary></indexterm> Aggregates are also very useful in combination with <literal>GROUP - BY</literal> clauses. For example, we can get the maximum low - temperature observed in each city with: + BY</literal> clauses. For example, we can get the number of readings + and the maximum low temperature observed in each city with: <programlisting> -SELECT city, max(temp_lo) +SELECT city, count(*), max(temp_lo) FROM weather GROUP BY city; </programlisting> <screen> - city | max ----------------+----- - Hayward | 37 - San Francisco | 46 + city | count | max +---------------+-------+----- + Hayward | 1 | 37 + San Francisco | 2 | 46 (2 rows) </screen> which gives us one output row per city. Each aggregate result is computed over the table rows matching that city. We can filter these grouped - rows using <literal>HAVING</literal> and the output count using - <literal>FILTER</literal>: + rows using <literal>HAVING</literal>: <programlisting> -SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo < 30) +SELECT city, count(*), max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40; </programlisting> <screen> - city | max | count ----------+-----+------- - Hayward | 37 | 5 + city | count | max +---------+-------+----- + Hayward | 1 | 37 (1 row) </screen> @@ -749,12 +748,18 @@ SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo < 30) names begin with <quote><literal>S</literal></quote>, we might do: <programlisting> -SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo < 30) +SELECT city, count(*), max(temp_lo) FROM weather WHERE city LIKE 'S%' -- <co id="co.tutorial-agg-like"/> - GROUP BY city - HAVING max(temp_lo) < 40; + GROUP BY city; </programlisting> + +<screen> + city | count | max +---------------+-------+----- + San Francisco | 2 | 46 +(1 row) +</screen> <calloutlist> <callout arearefs="co.tutorial-agg-like"> <para> @@ -791,6 +796,34 @@ SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo < 30) because we avoid doing the grouping and aggregate calculations for all rows that fail the <literal>WHERE</literal> check. </para> + + <para> + Another way to select the rows that go into an aggregate + computation is to use <literal>FILTER</literal>, which is a + per-aggregate option: + +<programlisting> +SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo) + FROM weather + GROUP BY city; +</programlisting> + +<screen> + city | count | max +---------------+-------+----- + Hayward | 1 | 37 + San Francisco | 1 | 46 +(2 rows) +</screen> + + <literal>FILTER</literal> is much like <literal>WHERE</literal>, + except that it removes rows only from the input of the particular + aggregate function that it is attached to. + Here, the <literal>count</literal> aggregate counts only + rows with <literal>temp_lo</literal> below 45; but the + <literal>max</literal> aggregate is still applied to all rows, + so it still finds the reading of 46. + </para> </sect1> |