Q: COUNT number of rows on table with LEFT JOIN returns 1 when not exists I've two tables, for example:
post:
id | author | content | date
1  | Lucas  | Hello!  | 2016
2  | Igor   | Hi!     | 2016

comment:
id | post_id | content | date
1  | 2       | hehehe  | 2016
2  | 1       | hahaha  | 2016
3  | 2       | huhuhu  | 2016

And I to do a SELECT that return all posts and a COUNT of rows of all comments with post.id = comment.id.
So, I tried:
SELECT p.id, p.author, p.content, p.date, COUNT(*) AS numComments FROM post p LEFT JOIN comment ON p.id = post_id WHERE p.author = '$author' GROUP BY p.id DESC LIMIT 12

And I got do it. But, even when no exists comments with p.id = post_id he returns 1.
So, I tried:
SELECT p.id, p.author, p.content, p.date, CASE WHEN COUNT(*) < 1 THEN '0' ELSE COUNT(*) END AS numComments FROM post p LEFT JOIN comment ON p.id = post_id WHERE p.author = '$author' GROUP BY p.id DESC LIMIT 12

But the result is the same. How to do this?

A: you can get count by this way, also last is order by not group by:
SELECT p.id, p.author, p.content, p.date, 
(select COUNT(*) from comment where p.id = comment.post_id) AS numComments FROM post p 
WHERE p.author = '$author' 
ORDER BY p.id DESC LIMIT 12


A: As outer joins return a row even if there's no matching data you need to count a column from the inner table, usually it's the column used in join:
SELECT p.id, p.author, p.content, p.date, COUNT(post_id) AS numComments
FROM post p LEFT JOIN comment ON p.id = post_id 
WHERE p.author = '$author' 
GROUP BY p.id -- seems to be mysql, otherwise you need to add more columns to the list

If you don't want to show rows with a zero count simply switch to an 
INNER JOIN.
