Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to return 0 instead of null when counting?

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

but it still returns null.

Comments model:

 id pk,

 post_id fk,

 description text,

Posts model:

id pk,
title varchar,
description text,

>Solution :

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)
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading