Joins Multiply Rows

If one row on the left matches three on the right, you get three rows. That is correct, and it catches everyone.

Octavia Butler has three books. Join authors to books and her row appears three times. Now SUM anything from the authors side and it is tripled.

Five authors become eight rows, because the join produced one row per book. Neither number is wrong; they answer different questions.

Two fixes, depending on the question. Aggregate before joining, so each side contributes one row. Or count the thing you actually mean with COUNT(DISTINCT a.id).

What you should not do is add DISTINCT to make the row count look right. The rows collapse and any SUM in the query stays inflated, which turns an obviously wrong answer into a plausible one.