mysql - embed count query within another query

In postgres I am fairly sure you can do something like this

SELECT
  authors.stage_name, 
  count(select id from books where books.author_id  = authors.id) 
FROM
  authors,
  books;

Essentially, in this example I would like to return a list of authors and how many books each has written.... in the same query.

Is this possible? I suspect this approach is rather naive..

Thanks :)

From stackoverflow

• How about using a join:

  SELECT authors.stage_name, count(*) 
  FROM authors INNER JOIN books on books.author_id = authors.id
  GROUP BY authors.stage_name
  

• Well, for one thing, it returns a Cartesian product of all authors to all books, regardless of whether that author wrote that book.

  Here's how I'd write a query to get the result you say you want:

  SELECT a.stage_name, COUNT(b.id)
  FROM authors a
    LEFT OUTER JOIN books b ON (a.id = b.author_id)
  GROUP BY a.id;
  

  You need to learn how to write join queries if you use SQL. The join is to SQL what the loop is to application programming languages. I.e. it's a fundamental programming construct that you need to know.