Skip to content
Advertisement

Join count query into one

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

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.