Please help with SQL query

1

I need to write an SQL query that: “For each library branch, retrieve the branch name and the total number of books loaned out from that branch.”

MY QUERY:

SELECT branchname, tbl_book_loans.bookid, dateout, datedue, COUNT(tbl_book_loans.branchid) AS loans_out FROM tbl_library_branch
INNER JOIN tbl_book_loans ON tbl_library_branch.branchid = tbl_book_loans.branchid
GROUP BY tbl_library_branch.branchname, tbl_book_loans.bookid, tbl_book_loans.dateout, tbl_book_loans.datedue
;

Please see my table that corresponds to the sql query above.loansbybranch

MY ISSUE:
The table I have created shows a separate line item for each book that each branch has loaned out. I need to aggregate the loans into one line item that will indicate how many books a branch has loaned out NOT broken down into loans-by-book, as I have done in my table. There should be 5, one for each branch, without any details on which books have been loaned out.

2

is it because (and my SQL is rather rusty) you have “tbl_book_loans.bookid” in your SELECT statement and also in your GROUP ID. If you just want a total of books that a library has loaned out then you dont need it (not sure either what the dates are there for)

3

It says only “retrieve the branch name and the total number of books loaned out from that branch”. But in your code you include Bookid, dateout and datedue. It should be:

SELECT branchname , COUNT(tbl_book_loans.branchid) AS loans_out
FROM tbl_library_branch
INNER JOIN tbl_book_loans ON tbl_library_branch.branchid = tbl_book_loans.branchid
GROUP BY tbl_library_branch.branchname