Join count query into one

Question

I have two very simple count queries that I would like to join into one.

To clarify the situation I will add the tables I got:

Book

isbn	author
1111	GB
2222	DC
3333	RL

Author

code	Name
GB	George B.
KL	Kyle L.
DC	Donald C.
RL	Roland L.

Coauthor

name	isbn
KL	1111
GB	2222
GB	3333

And the queries I made: Query 1:

SELECT a.name, count(*) 
FROM coauthor c INNER JOIN author a ON c.name = a.code
GROUP BY a.name

Gives:

name	Count(*)
KL	1
GB	2

Query 2:

SELECT a.name, count(*) 
FROM author a INNER JOIN book b ON a.code = b.author
GROUP BY a.name

Gives:

name	count(*)
GB	1
DC	1
RL	1

And so I would like to get:

name	count(*)
George B.	3
Donald C.	1
Roland L.	1
Kyle L.	1

I have been searching the web, but I guess I don't know where to start.

Answer 1

You can join Author to Book and Coauthor with LEFT joins and aggregate:

SELECT a.code, a.name, 
       COUNT(DISTINCT b.isbn) + COUNT(DISTINCT c.isbn) count
FROM Author a
LEFT JOIN Book b ON b.author = a.code
LEFT JOIN Coauthor c ON c.name = a.code
GROUP BY a.code, a.name;

See the demo.