postgres.git / summary / log / commit / refs
commit fede15417225c08479fc6cd10812a25826c65f86
Author: Bruce Momjian <bruce@momjian.us>
Date: Thu Sep 01 02:19:06 2022 +0000
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 6f31a5a1d62..8243bf51af8 100644
--- a/doc/src/sgml/query.sgml
+++ b/doc/src/sgml/query.sgml
@@ -726,19 +726,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>
@@ -748,7 +749,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: fbd597e0b80d]