I'm stuck on a problem that should be simple. Suppose I have two tables: authors and books. For this example we will assume that a book has only one author. I'd like to have a report that expresses the amount authors having written x books, e.g:

...

The thing is that when I (outer) join the books and authors I get NULL values for the books.

My best approach was to do it in two queries, one with a inner join so I exclude the authors not having written a book yet and then I add the count of them programmatically.

Here's my query

select
  book_count,
  count(*)
from (
  select
    a.id,
    count(*) as book_count
  from authors a
  join books b
  on a.id = b.author_id
  group by a.id
) a
group by book_count

How can I do this in only one query ?

EDIT: here's a minimal example

create table authors (
    id int primary key,
    name varchar(100)
);

create table books (
    id int primary key,
    title varchar(255),
    author_id int
);    
    
insert into authors values 
    (1, 'Isaac Asimov'),
    (2, 'Ray Bradbury'),
    (3, 'Aldous Huxley'),
    (4, 'Bruno Dusausoy');

insert into books values
    (1, 'Foundation', 1),
    (2, 'Foundation and Empire', 1),
    (3, 'Second Foundation', 1),
    (4, 'Fahrenheit 451', 2),
    (5, 'Brave New World', 3);

With the query mentioned above, I don't get the row 0, 1

If I do a left join I get

So NULL are not the same as 0

CodePudding user response：

How about left joining the books to the authors.
Then the authors without books will have a 0 book_count.

select
  book_count,
  count(*) as amount
from (
  select
    author.id as author_id,
    count(book.id) as book_count
  from authors as author
  left join books as book
    on book.author_id = author.id
  group by author.id
) a
group by book_count
order by book_count

db<>fiddle here