coalesce with filter function in postgresql-CodePudding

I am running this query:

SELECT
     SUM(PRICE) FILTER (my filter's parameters)

and when there's no record it returns <null>

then I tried:

SELECT
       COALESCE(SUM(PRICE),0) FILTER (my filter's parameters)

But then I get an error FILTER not valid for coalesce function

my filter's parameters are time window, like this:

FILTER(WHERE date(order_date) >= (cast(current_date as date) - interval '1' day)) as day1

How can I replace <null> by 0 when there's no record?

CodePudding user response：

You should try wrapping the entire FILTER expression in COALESCE, e.g.

SELECT
    COALESCE(SUM(PRICE) FILTER (my filter's parameters), 0) AS sum_price