Re: nicer examples for aggregate calls

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 <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

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