postgres.git / summary / log / commit / refs
commit d55fdfb554348ffd8e3afc61d62523a8f7d4a78e
Author: Bruce Momjian <bruce@momjian.us>
Date: Thu Sep 01 02:19:06 2022 +0000
First released in: 16.0, 10.23, 11.18, 12.13, 13.9, 14.6, 15.0
doc: use FILTER in aggregate example
Reported-by: michal.palenik@freemap.sk
Discussion: https://postgr.es/m/163499710897.684.7420075366995883688@wrigleys.postgresql.org
Backpatch-through: 10
doc/src/sgml/query.sgml | 13 +++++++------
1 file changed, 7 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml
index d58be9c969d..1606396c000 100644
--- a/doc/src/sgml/query.sgml
+++ b/doc/src/sgml/query.sgml
@@ -728,19 +728,20 @@ SELECT city, max(temp_lo)
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>:
+ rows using <literal>HAVING</literal> and the output count using
+ <literal>FILTER</literal>:
<programlisting>
-SELECT city, max(temp_lo)
+SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo < 30)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;
</programlisting>
<screen>
- city | max
----------+-----
- Hayward | 37
+ city | max | count
+---------+-----+-------
+ Hayward | 37 | 5
(1 row)
</screen>
@@ -750,7 +751,7 @@ SELECT city, max(temp_lo)
names begin with <quote><literal>S</literal></quote>, we might do:
<programlisting>
-SELECT city, max(temp_lo)
+SELECT city, max(temp_lo), count(*) FILTER (WHERE temp_lo < 30)
FROM weather
WHERE city LIKE 'S%' -- <co id="co.tutorial-agg-like"/>
GROUP BY city
[parent: 4e3eb6dd1a9b]