SQL: Count amount of comments for each post written by specific author

Question

I always felt like I needed to improve my SQL knowledge, I am trying to, but still no success to find an answer to my question.

I have 2 tables: Post and Comments.

Post table has a PK PostID, Username, Title, Content.

Comments table has a PK CommentID, FK PostID, Username, Content.

Username in PostID is the Username of the post's author, in Comments it is commenter's username. Everything else should be pretty self explanatory.

Main challenge: I want to select all posts made by that specific author and display amount of comments for each post AND display zero if no comments are found. All in one query (if possible?)

So far the closest, I've gotten to was this:

SELECT p.*, COUNT(*) as CommentAmount
FROM Posts p
LEFT OUTER JOIN Comments c
ON p.PostID = c.PostID
WHERE p.Username = 'author1'
GROUP BY c.PostID

It works in a way, but doesn't display neither Post data, nor CommentAmount (0) if no comments exist for that PostID - which makes sense since it does not find any p.PostID that has the same c.PostID. How to make it display 0 CommentAmount as well? Is it even possible?

Here is the sample SQL Fiddle I've set up so you could test as well!: http://sqlfiddle.com/#!9/f8941

UPDATE: I made a little mistake in the schema... sorry guys. Fixed in the fiddle above^

UPDATE2: Thanks everyone for amazing answers! Funny how most solutions work perfectly on SQL Fiddle but seem not to work on my DB on the cloud, using MySQL Workbench... I'll have to look into that now then, thanks everyone!

Answer 1

How about this?:

SELECT p.*,
  (SELECT COUNT(*) FROM Comments WHERE PostID=p.PostID) AS num_comments
FROM Posts p
WHERE p.Username = 'author1'

Answer 2

You only need to adjust your query by adding GROUP BY and changing COUNT() aggregate argument to point to Comments so that it will enable storing value 0 when there are no comments for a particular post.

It doesn't matter which column from Comments you will put inside COUNT(), since every column has a NULL value when JOIN condition is not met.

SELECT p.*, COUNT(c.CommentID) AS CommentAmount
FROM Posts p
LEFT JOIN Comments c
ON p.PostID = c.PostID
WHERE p.username = 'author1'
GROUP BY 1,2,3,4