Re: nicer examples for aggregate calls

[Bruce Momjian]

On Sat, Oct 23, 2021 at 01:51:48PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/14/tutorial-agg.html
> Description:
> 
> currently, all of the examples are very simple, like
> 
> SELECT city, max(temp_lo)
>     FROM weather
>     WHERE city LIKE 'S%'            -- (1)
>     GROUP BY city
>     HAVING max(temp_lo) < 40;
> 
> this example would be more complex and would allow users to search for
> clause "filter":
> 
> Finally, if we only care about cities whose names begin with “S” and we want
> to calculate the number of observations in each city with temp_lo over 30,
> we might do:
> 
> SELECT city, max(temp_lo), count(*) filter (temp_lo>30), 
>     FROM weather
>     WHERE city LIKE 'S%'            -- (1)
>     GROUP BY city
>     HAVING max(temp_lo) < 40;

Good idea.  We didn't support FILTER at the time this query was added.
Here is a patch which adds it.

-- 
  Bruce Momjian  <br...@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson


diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml
index 6f31a5a1d6..8243bf51af 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 &lt; 30)
     FROM weather
     GROUP BY city
     HAVING max(temp_lo) &lt; 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 &lt; 30)
     FROM weather
     WHERE city LIKE 'S%'            -- <co id="co.tutorial-agg-like"/>
     GROUP BY city