CODEWITHSUNDEEP
mysqlsql
user1760790
user1760790

Reputation: 81

count and a sub select

i have a select that brings up title, bookcopiesid, and name. 

select 
    books.title, borrow.bookcopiesid, users.name, usersid,library_locations.name, checkout_date, return_date, due_date 
FROM 
    books,  borrow,users, library_locations, userlib 
WHERE 
    library_locations.id = userlib.libid 
AND 
    userlib.userid = users.id 
AND 
    borrow.bookcopiesid = books.bookid 
AND 
    borrow.usersid = users.id and return_date is not null ;

how would i get something like 

SELECT title, COUNT(*) as count 
FROM (
    SELECT books.title, borrow.bookcopiesid, users.name, usersid,library_locations.name, checkout_date, return_date, due_date 
    FROM books,  borrow,users, library_locations, userlib 
    WHERE library_locations.id = userlib.libid and userlib.userid = users.id and borrow.bookcopiesid = books.bookid and borrow.usersid = users.id and return_date is not null) 
GROUP BY title 
ORDER BY count DESC);

to work.

i am trying to display the number of titles for each name

Upvotes: 1

Views: 148

Answers (1)

Thomas Kelley
Thomas Kelley

Reputation: 10302

I think this is what you're looking for?

SELECT
    books.title,
    COUNT(*) as count
FROM
    books, 
    borrow,
    users, 
    library_locations,
    userlib 
WHERE 
    library_locations.id = userlib.libid 
    AND userlib.userid = users.id 
    AND borrow.bookcopiesid = books.bookid 
    AND borrow.usersid = users.id 
    AND return_date is not null
GROUP BY books.title 
ORDER BY COUNT(*) DESC;

No need for a sub-query; you just need to qualify the columns in your SELECT and GROUP BY clauses (just like you did in your WHERE clause).

Also, return_date needs to be qualified... But I don't know which table that came from, so you can add that in yourself.

Upvotes: 1

Related Questions