How to count the amount of distinct occurrences in a BD and return

Asked

Viewed 58 times

1

I have 3 tables in a library database. One is the member table, with the age of that member and one PRIMARY KEY. The second table, book, has a PRIMARY KEY isbn and the rest of the attributes authors and publishing house. The third table is a borrowed table, where it has the attributes isbn, as being a foreign key for the isbn attribute of the table book and the attribute member, being a foreign key of the member attribute.

CREATE TABLE livro(
isbn BIGINT PRIMARY KEY,
autores VARCHAR(25) NOT NULL,
editora VARCHAR(20) NOT NULL
);

CREATE TABLE emprestado(
num_membro INT NOT NULL,
isbn BIGINT NOT NULL,
FOREIGN KEY (num_membro) REFERENCES membro(num_membro),
FOREIGN KEY (isbn) REFERENCES livro(isbn));

I need to make a query to find the authors who had books taken for loan more than 5 times, and group them by publisher.

I made two ways:

SELECT l.editora, l.autores
FROM emprestado e INNER JOIN livro l ON e.isbn = l.isbn
WHERE (SELECT COUNT(l.autores)
       FROM emprestado e INNER JOIN livro l ON e.isbn = l.isbn) > 5
GROUP BY l.editora, l.autores;

In this first case, the table also ends up returning other authors who have not had books picked more than 5 times.

SELECT l.editora, l.autores
from livro l JOIN emprestado e ON e.isbn = l.isbn
GROUP by l.editora, l.autores
HAVING COUNT(l.autores) > 5;

This second case, is not returned the authors in which their books have different publishers, that is, even if these authors have books taken more than 5 times, if their publishers are different it does not appear.

I would like to know how to make my query return the authors who had books picked more than 5 times, both having equal publishers, as distinct.

Grateful!!

sql database postgresql query group-by
  • 1

    I believe your joint condition ON e.num_membro = l.isbn is wrong, shouldn’t be ON e.isbn = l.isbn? In the second query I believe you should not include autores in its GROUP BY clause.

  • I saw the wrong JOIN and tidied up (thanks), but still did not show the author that has different publishers. About the second query, I took l.authors from GROUP BY, it informs me "42803 column"l. authors" must appear in the GROUP BY clause or be used in an Aggregate Function", and even if I take this l.authors also from SELECT, it still does not return me those publishers of the authors I want, only the one that has the same publisher.

1 answer

0


If you are using Postgresql 9.5 or later you can use the Count function with a "window Function" to get the amount per author:

with CTE_Count as
(
  SELECT 
    l.editora, 
    l.autores, 
    count(*) over(partition by l.autores) as Quantidade
  from livro l 
  inner JOIN emprestado e ON e.isbn = l.isbn
)

select editora, autores
from CTE_Count
where Quantidade > 5
GROUP by editora, autores

I hope it helps

Browser other questions tagged sql database postgresql query group-by

You are not signed in. Login or sign up in order to post.