COUNTIF Question

[Jassim Rahma]

Hi,

Ho can I do COUNTIF based on below SQL? It's returning wrong count for this line:

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


SELECT channels.channel_id, channels.channel_uuid, channels.channel_name,
country.country_name_english AS country_name, regions.region_name_english As region_name,
COUNT(posts.post_id) AS total_posts,
COUNT(IF(posts.is_published = TRUE, posts.post_id, 0)) AS total_published
FROM channels
JOIN country ON country.country_code = channels.country_code
JOIN regions ON regions.region_id = country.region_id
LEFT JOIN posts ON posts.channel_id = channels.channel_id
WHERE owner_id = 1001;