How to count different columns from different tables using SQL JOINS

Question

I am trying to get the count of item ids satisfying a condition from two tables joined in SQL join but I can't be able to get it work. When I exclude DISTINCT, it gives me some large and unknown figures, but with DISTINCT, it gives me the correct result but it takes too long. How can I optimize it? Is there any other way I can approach it?

SELECT 
    COUNT(DISTINCT b.id) AS book_count,
    sq.name,
    sq.id,
    COUNT(DISTINCT s.id) FILTER(WHERE s.graduated=$1) AS s_count, 
    sq.invoice_date,  
    sq.subscribed,
    sq.installed
FROM sq
JOIN student AS s ON s.id = sq.id\
JOIN books AS b ON b.id=sq.id
GROUP BY sq.id, sq.invoice_date, sq.subscribed, sq.installed

Answer 1

You would probably need to aggregate in subqueries before joining - or better yet, we can use inline subqueries, since all you want is a count from each related table:

select sq.*,
    (select count(*) from students s where s.id = sq.id and s.graduated = $1) s_count
    (select count(*) from books b where b.id = sq.id) book_count
from sq

For performance, consider the following indexes so the subqueries execute efficiently:

student(id, graduated) -- or student(graduated, id)
book(id)