From: Bruce Momjian <bruce@momjian.us>
To: michal.palenik@freemap.sk
To: pgsql-docs@lists.postgresql.org
Subject: Re: nicer examples for aggregate calls
Date: Wed, 17 Aug 2022 12:20:10 -0400
Message-ID: <Yv0VOqChCHRp1nII@momjian.us> (raw)
In-Reply-To: <163499710897.684.7420075366995883688@wrigleys.postgresql.org>
References: <163499710897.684.7420075366995883688@wrigleys.postgresql.org>
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@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Attachments:
[text/x-diff] filter.diff (1.2K, ../Yv0VOqChCHRp1nII@momjian.us/2-filter.diff)
download | inline diff:diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgmlindex 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 < 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, michal.palenik@freemap.sk, pgsql-docs@lists.postgresql.org
Subject: Re: nicer examples for aggregate calls
In-Reply-To: <Yv0VOqChCHRp1nII@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox