pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feednicer examples for aggregate calls
3+ messages / 2 participants
[nested] [flat]
* nicer examples for aggregate calls
@ 2021-10-23 13:51 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Doc comments form @ 2021-10-23 13:51 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: michal.palenik@freemap.sk
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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: nicer examples for aggregate calls
@ 2022-08-17 16:20 Bruce Momjian <bruce@momjian.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Bruce Momjian @ 2022-08-17 16:20 UTC (permalink / raw)
To: michal.palenik@freemap.sk; pgsql-docs@lists.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.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 < 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: nicer examples for aggregate calls
@ 2022-09-01 02:21 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2022-09-01 02:21 UTC (permalink / raw)
To: michal.palenik@freemap.sk; pgsql-docs@lists.postgresql.org
On Wed, Aug 17, 2022 at 12:20:10PM -0400, Bruce Momjian wrote:
> 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.
Patch applied back to PG 10. Thanks.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-09-01 02:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-23 13:51 nicer examples for aggregate calls PG Doc comments form <noreply@postgresql.org>
2022-08-17 16:20 ` Bruce Momjian <bruce@momjian.us>
2022-09-01 02:21 ` Bruce Momjian <bruce@momjian.us>
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