Get Count 0 instead of NULL MySQL Query

Question

I've got a query that counts the number of posts in a month with a certain tag, but obviously my WHERE won't return anything if there are no posts. The problem with that, I have overlayed on the chart another query that shows ALL posts, not just the ones who meet these WHERE conditions. The second query will typically have a result for every month. How can I get this query to return zero if the result is null? I've read a few of the answers here, but can't quite figure it out.

SELECT MONTHNAME(post_time) AS month, 
       COALESCE(Count(distinct p.post_id), 0) AS count 
FROM post.p 
INNER JOIN post_tag_map t ON ( p.post_id = t.knote_id ) 
WHERE t.post_id IN (23,24,49,54) 
/*these numbers are actually a variable, this is just an example*/  
AND p.post_time >= (CURDATE() - INTERVAL 1 YEAR)

I'm trying to show both Months and Posts from the beginning of time of the database.

Barmar · Accepted Answer · 2014-04-20 05:19:40Z

4

Use a LEFT JOIN to include rows with no match:

SELECT MONTHNAME(post_time) AS month, 
       COALESCE(COUNT(distinct t.knote_id), 0) AS count 
FROM post AS p 
LEFT JOIN post_tag_map AS t 
ON p.post_id = t.knote_id AND t.post_id IN (23,24,49,54) 
WHERE p.post_time >= (CURDATE() - INTERVAL 1 YEAR)
GROUP BY month

Note that you have to put the t conditions in the ON clause, not WHERE, when using LEFT JOIN; otherwise, they'd fail on the non-matching rows. And you have to count the column from t, so it will skip the null value for the non-matching row.

DEMO

edited Apr 20, 2014 at 5:19

answered Apr 19, 2014 at 16:06

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

dcclassics Over a year ago

This looked right, but it's still not returning anything for March (0 posts total, so that makes sense to me but not desired result).

Barmar Over a year ago

You need to join with a table that contains the month. See stackoverflow.com/questions/23130805/… for a way to create it on the fly.

dcclassics Over a year ago

Ok. The same table does contain the months. They show up if i dont do the WHERE IN. If that makes sense.

Barmar Over a year ago

There's no WHERE IN in my answer. I moved the IN condition to the ON clause to solve that problem.

dcclassics Over a year ago

Err - I meant...the table that contain the posts, there are posts from every month. Just not necessarily those tagged with this particular tag (23,24,49,54). If I run this query without the IN, those months show. I was wondering if I could do that without creating a separate table just for the months.

|

pabloasc · Accepted Answer · 2014-04-19 16:14:23Z

0

Try using the ISNULL(amount,0) function:

SELECT MONTHNAME(post_time) AS month, 
ISNULL(Count(distinct p.post_id), 0) AS count 
FROM post.p 
INNER JOIN post_tag_map t ON ( p.post_id = t.knote_id ) 
WHERE t.post_id IN (23,24,49,54) 
/*these numbers are actually a variable, this is just an example*/  
AND p.post_time >= (CURDATE() - INTERVAL 1 YEAR)

answered Apr 19, 2014 at 16:14

pabloasc

7835 silver badges11 bronze badges

Collectives™ on Stack Overflow

Get Count 0 instead of NULL MySQL Query

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related