Quote
COUNT(IF(posts.is_published = TRUE, posts.post_id, 0)) AS total_published
For example when I have 3 total posts and only one has is_published = TRUE I am still getting 3 as published_post, so the below code returns total count for both total_posts and published_posts
Let's work this through:
select * from posts ;
+---------+--------------+
| post_id | is_published |
+---------+--------------+
| 1 | FALSE |
| 2 | FALSE |
| 3 | TRUE |
select IF(posts.is_published = TRUE, posts.post_id, 0)
from posts ;
+-------+
| IF... |
+-------+
| 0 |
| 0 |
| 3 |
select COUNT(IF(posts.is_published = TRUE, posts.post_id, 0)) AS total_published
from posts ;
+-----------------+
| total_published |
+-----------------+
| 3 |
+-----------------+
Remember - count() does what it says on the tin - it counts non-null values and your result set is giving it three such values, zero, zero and three.
In this case, your simplest solution might be this:
select SUM(IF(posts.is_published = TRUE, 1, 0)) AS total_published
from posts ;
+-----------------+
| total_published |
+-----------------+
| 1 |
+-----------------+
Regards, Phill W.