How to return 0 instead of null when counting?-CodePudding

I have a query:

with comments_info as (
        SELECT p.post_id as post_id,
               COUNT(p.id) as comments
        FROM post_comments p
        GROUP BY post_id
    )
select p.id as post_id, c.comments
from posts p
             left join comments_info c on c.post_id = p.id

But if there are no comments on the post, then count returns null. I tried to do like this:

CASE WHEN COUNT(p.id) IS NULL THEN '0' ELSE COUNT(p.id) END as comments

but it still returns null.

Comments model:

 id pk,

 post_id fk,

 description text,

Posts model:

id pk,
title varchar,
description text,

CodePudding user response：

Try with COALESCE by wrapping your count, see

COALESCE(count(p.id),0) as comments

As per comment:

use COALESCE in the outer query:

COALESCE (p.comments, 0)

CodePudding user response：

Use COALESCE in the outer query:

-- with clause omitted
select
  p.id as post_id,
  coalesce(c.comments, 0) as comments
-- from, join omitted

When there are no comments for a post, its count will not be null; there won't be a row, so the left join will return null for all columns of comments_info.

coalesce() returns the first non-null value of its parameters.