Can't figure out how to find how many books were loaned by each library branch-CodePudding

The relation between the two tables is,

CREATE TABLE library_branch 
(
    branchID NUMBER, 
    branchName varchar(20), 
    address varchar(20), 
    PRIMARY KEY(branchID)
)

CREATE TABLE book_loan 
(
    bookID varchar(20), 
    branchID NUMBER, 
    cardNo NUMBER, 
    dateOut DATE, 
    dueDate DATE, 
    PRIMARY KEY(bookID, branchID, cardNo), 
    FOREIGN KEY(branchID) REFERENCES library_branch(branchID)
)

The question is: for each library branch, retrieve the branch name and the total number of books loaned out from that branch

I have written a query to find out which library branch loaned which books but can't figure out how to find how many books were loaned by each library branch.

Any help to solve this problem will be appreciated.

CodePudding user response：

select count(bookId), branchName from library_branch
outer join book_loan on book_loan.branchId = library_branch.branchId
group by branchName

This selects all rows from library_branch, then joins those rows with any matching rows from book_loan. It then counts any rows with a non-null bookId and sums those counts by branch.

Note that count(*) would be incorrect, as it would return at least 1 for each branch even if no books were loaned.

CodePudding user response：

"Books per branch" translates to GROUP BY branchid in SQL. Well, and "how many" is COUNT(*). You get that information from the book_loan table. To find out how many books are loaned at the moment, look at the dates. The loans in question will have a dateOut of today or in the past and a dueDate in the future.

In Oracle you get the current day with TRUNC(SYSDATE).

At last you want information about the branch itself. So select that information from the branch table, then join the subquery that gets you the count per branch. Use an outer join so as to include the branches that have not currently loaned any books.

select b.branchid, b.branchname, coalesce(l.loaned, 0) as currently_loaned
from library_branch b
left outer join
(
  select branchid, count(*) as loaned
  from book_loan
  where dateout <= trunc(sysdate)
  and duedate > trunc(sysdate)
  group by branchid
) l on l.branchid = b.branchid
order by b.branchid;

CodePudding user response：

Starting from your query to know which books have been loaned from which branch, the next step is to group by the branch and count the amount of books loaned. This can be achieved with:

SELECT branchName, COUNTDISTINCT(bookId) as booksLoaned
FROM library_branch lb INNER JOIN book_loan bl ON lb.branchId = bl.branchId
GROUP BY branchName

I used COUNTDISTINCT because you mentioned you need the number of books loaned not the number of loans, so with that each bookId is only counted once. If you need the total number of loans you can just use COUNT instead.

EDIT:

SELECT branchName, COUNTDISTINCT(bookId) as booksLoaned
FROM library_branch lb LEFT OUTER JOIN book_loan bl ON lb.branchId = bl.branchId
GROUP BY branchName

change the inner join to a left outer join if you want to also return branches that have not loaned any books.