The problem is that if there is 0 comment or 1 comment the count shows 1 while the rest is working well means that 2, 3, etc working fine.
$sql = "SELECT blog.*,count(blog.id) as Total FROM blog left JOIN comment on comment.id = blog.id GROUP BY date desc";
Your query should look like this:
SELECT b.date, count(c.id) as Total FROM blog b LEFT JOIN comment c ON c.id = b.id GROUP BY b.date DESC;
This assumes that
date comes from
blog (which should be the case if your current query is working). The difference is that you are counting from the second table, not the first.
This does not use
* for columns from
blog. That is usually a very, very bad idea when using
GROUP BY. The best practice (enforced by almost all SQL engines) is to only include unaggregated columns in the
SELECT when they are in the
Note: It seems very awkward that the same column
id is used for the
JOIN between two very different entities (blogs and comments).