Correct COUNT in SQL query?

Question

PHP

Hello!
The query to the database is formed in the following way:

$database = Database::openConnection();
       $query  = "SELECT posts.id AS id, users.profile_picture, users.id AS user_id, users.name AS user_name, posts.title, posts.content, posts.date, COUNT(*) AS comments ";
       $query .= "FROM users, posts, comments ";
       $query .= "WHERE posts.id = :id ";
       $query .= "AND users.id = posts.user_id LIMIT 1 ";

COUNT(*) AS comments displays the number of records in the comments table, but how can I make it display the number of comments where post_id = :post_id? I don't quite understand.
I tried this:

$database = Database::openConnection();
       $query  = "SELECT posts.id AS id, users.profile_picture, users.id AS user_id, users.name AS user_name, posts.title, posts.content, posts.date, COUNT(*) AS comments ";
       $query .= "FROM users, posts, comments ";
       $query .= "WHERE posts.id = :id ";
       $query .= "AND comments  = :post_id ";
       $query .= "AND users.id = posts.user_id LIMIT 1 ";

       .....
      $database->bindValue(':post_id ', $post_id );

But it gives an error.
And such question: whether strong loading on a DB will be? Each time you open a blog post, each time there will be a selection of count across the entire table with comments

Answer 1

Count is a group function. For a query with Count to work, one of the following conditions must be met:
1. Count - only one field in the selection list. i.e. SELECT count(*) from ...
2. Grouped by all attributes in the selection list. SELECT fio, bdate, count(*) from tab group by fio,bdate.
The second case seems to cover your needs.
In your case, I would also look at the analytical functions COUNT(..) OVER (PARTITION BY ...) IMHO it will work faster than COUNT(..) GROUP BY..., but without understanding the task, I don’t know how much you can use in your case