Feature request: FILTER on aggregate functions

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Feature request: FILTER on aggregate functions

Shawn Wagner
I just found out that postgres (And possibly others?) supports FILTER on
aggregate functions in general, not just when they're used as a window
function.

Trivial example:

    SELECT count(*), count(*) FILTER (WHERE amount > 100) FROM blah

which is a lot cleaner than

    SELECT count(*), sum(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM blah

Would be nice to have someday...
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: FILTER on aggregate functions

Igor Tandetnik-2
On 5/6/2019 5:19 PM, Shawn Wagner wrote:

> I just found out that postgres (And possibly others?) supports FILTER on
> aggregate functions in general, not just when they're used as a window
> function.
>
> Trivial example:
>
>      SELECT count(*), count(*) FILTER (WHERE amount > 100) FROM blah
>
> which is a lot cleaner than
>
>      SELECT count(*), sum(CASE WHEN amount > 100 THEN 1 ELSE 0 END) FROM blah

sum(amount > 100)   is sufficient.
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users