MySQL Forums
Forum List  »  Newbie

Re: COUNTIF Question
Posted by: Phillip Ward
Date: July 15, 2021 07:56AM

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.

Options: ReplyQuote


Subject
Written By
Posted
July 15, 2021 05:57AM
Re: COUNTIF Question
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.