List a number of copies per book

For a school project, I should do queries on this Library model.

I’ve done good so far, but now I’m stuck on this question: “List a number of copies per book”.

Here’s what’ve done and not working as it should:

SELECT liv_titulo, ( 
    SELECT count(exe_cod)
    FROM exemplar
    GROUP BY liv_cod
    ORDER BY COUNT(exe_cod) desc) Quantidade
FROM livro INNER JOIN exemplar USING (liv_cod);

Answer

I think the technique you wanted to use is a correlated subquery:

• you need a WHERE clause in the subquery so it only takes into account the liv_code from the current row in the outer query

• no GROUP BY clause is needed in the subquery; it should return a scalar value (just one row and one column, that contains the count of matching rows from table examplar)

• there is no need for a join in the outer query

Code:

SELECT 
    liv_titulo, 
    ( 
        SELECT count(*)
        FROM exemplar e
        WHERE e.liv_cod = l.liv_cod  -- correlation
    ) Quantidade
FROM livro l;