MySQL Forums
Forum List  »  Newbie

COUNTIF Question
Posted by: Jassim Rahma
Date: July 15, 2021 05:57AM


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;

Options: ReplyQuote

Written By
COUNTIF Question
July 15, 2021 05:57AM
July 15, 2021 07:56AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.