Walter Villa February 2016

Return zero instead NULL in subquery

I need to count the posts for each author. I'm using subquery for count them. But where an author has not post, the result is NULL, but I wish 0.

SELECT id, name,
    (SELECT COUNT(id)
    FROM posts
    WHERE post.author = authors.id
    GROUP BY author) as post_num
FROM authors
ORDER BY post_num DESC

How can I solve the problem?

Answers


Giorgos Betsos February 2016

Use COALESCE:

SELECT id, name,
       COALESCE((SELECT COUNT(id) 
                 FROM posts 
                 WHERE post.author = authors.id 
                 GROUP BY author), 0) as post_num
FROM authors 
ORDER BY post_num DESC


Eric February 2016

I think you can solve this by putting the inner select in an IsNull function.

Select id, name,
IsNull(SELECT COUNT(id) FROM posts
       WHERE post.author = authors.id
       GROUP BY author, 0) as post_num
FROM authors
ORDER BY post_num DESC

MSDN link

Basically if the first argument of the IsNull function is NULL, the second argument will be passed. If not, then the result of the firt argument will be passed.


Juan Carlos Oropeza February 2016

coalesce solve your question, but probably you will get better performance if you use a LEFT JOIN so consider try it.

 SELECT a.id, a.name, count(post.author) as post_num
 FROM authors a
 LEFT JOIN post p 
        ON a.id = p.author
 GROUP BY a.id

Post Status

Asked in February 2016
Viewed 3,382 times
Voted 12
Answered 3 times

Search




Leave an answer