How to return a default value in a query if SELECT returns NULL or returns nothing for a particular expression?

Question

PostgreSQL

Good afternoon!
For example, I want the bottom expression to return 0 to me or, say , 1 if the result of the rating expression returns NULL :

SELECT
                m_users.id,
                  ( SUM(mc_rating.name) / COUNT(mc_rating.name)) as rating ...

Answer 1

There are several solutions in documentation . The choice is yours.

Answer 2

select coalesce(   ( SUM(mc_rating.name) / COUNT(mc_rating.name) ), 0::integer ) as rating, ...

But I don’t understand, if rating is not null, then there will be 2 columns, and otherwise one?
Try a subquery:

with _rating as ( select
    m_users.id,
    coalesce(   ( SUM(mc_rating.name) / COUNT(mc_rating.name) ), 0::integer ) as rating ....
)
select * from _rating r where r.rating > 0