diff options
| author | Bruce Momjian <bruce@momjian.us> | 2022-08-31 22:19:06 -0400 |
|---|---|---|
| committer | Bruce Momjian <bruce@momjian.us> | 2022-08-31 22:19:06 -0400 |
| commit | d11a53a8e0a77c95c47e6f2514c1ba70c18d60e0 (patch) | |
| tree | 286d1bc96bc0f257b3268fbb88f0f81f4d6669ab | |
| parent | 9d8f19201a6e6b53590080b892b41f6cca6eec26 (diff) | |
| download | postgresql-d11a53a8e0a77c95c47e6f2514c1ba70c18d60e0.tar.gz postgresql-d11a53a8e0a77c95c47e6f2514c1ba70c18d60e0.zip | |
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
| -rw-r--r-- | doc/src/sgml/query.sgml | 13 |
1 files changed, 7 insertions, 6 deletions
diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml index 71d644f4323..9046d7c9fbe 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 |