MySQL count from two tables

Question

I have two tables: loan and librarian.

librarian: idLibrarian, libName and libPassword loan: idLoan,...,idLibrarian

Each loan has a idLibrarian. What I'm trying to do is find which librarian has the most loans. I can count number of librarians and number of loans. But i can't figure out how to group all the loans by idLibrarian and find the one with the highest amount of loans.

pap · Accepted Answer · 2012-03-22 12:13:26Z

1

select idLibrarian, count(*) from loan group by idLibrarian order by 2 desc

First row will be the librarian with the most loans, etc.

answered Mar 22, 2012 at 12:13

pap

27.7k6 gold badges44 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anton Gregersen Over a year ago

For some reason i get an error. This is what i've written:s.executeQuery("SELECT idLibrarian, COUNT(*) " + "FROM loan" + "GROUP BY idLibrarian" + "ORDER BY 2 DESC");

pap Over a year ago

Looks like you are missing some whitespaces, between load and GROUP and between idLibrarian and ORDER. "SELECT idLibrarian, COUNT(*) " + "FROM loan" + " GROUP BY idLibrarian " + "ORDER BY 2 DESC". Or you could skip concatenation and write it all in one string.

Collectives™ on Stack Overflow

MySQL count from two tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related