Better way to get counts of data in this query?

Question

I have a library of books (books) and another table where every time someone loans a book or brings a book back, it gets recorded (book_loans).

Basically I want to see for all books that were signed out at a certain time, how many:

were of the genre "fantasy"
had more than 500 pages
were hardcover books

I want to do this all at once and I'm using 4 queries right now, is there a better way?

PS: book_loans.bookID is a foreign key for books.id:

SELECT * FROM
(

    SELECT COUNT(*) FROM `books` AS bks
    INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
    WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1 AND bks.genre = 'fantasy'

    UNION

    SELECT COUNT(*) FROM `books` AS bks
    INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
    WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1 AND bks.pages > 500


    UNION

    SELECT COUNT(*) FROM `books` AS bks
    INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
    WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1 AND bks.hardcover = 1

) x;

It just seems very inefficient to be doing 4 queries total for this.

score 2 · Answer 1 · answered Jul 06 '18 at 19:14

2

SELECT SUM(bks.genre = 'fantasy') as fantasy,
       SUM(bks.pages > 500) as pages,
       SUM(bks.hardcover = 1) as hardcover
FROM `books` AS bks
INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1

answered Jul 06 '18 at 19:14

juergen d

201,996
37
293
362

Thank you so much! – Aran Bins Jul 06 '18 at 19:20

score 1 · Answer 2 · answered Jul 06 '18 at 19:15

This should do what you need if you just want total count where all 3 is or, if you need 3 different totals, one for each OR you need something different.

SELECT COUNT(*) FROM `books` AS bks
INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1 
AND (
    bks.genre = 'fantasy'
    OR bks.pages > 500
    OR bks.hardcover = 1
)

score 1 · Answer 3 · answered Jul 06 '18 at 19:19

SELECT CASE WHEN bks.genre = 'fantasy' THEN 'genre match' 
            WHEN bks.pages > 500 THEN 'page count met' 
            WHEN bks.hardcover = 1 THEN 'is hardcover'
       END AS condition
      , COUNT(*) 
FROM `books` AS bks
INNER JOIN `book_loans` AS bs ON bks.id = bs.bookID
WHERE bs.time = '2017-07-01 02:16:00' AND bs.signedout = 1 
  AND (bks.genre = 'fantasy' OR bks.pages > 500 OR bks.hardcover = 1)
GROUP BY condition;

This is an alternative to juergen d's with similar results; but I would go with his first. Unlike his, in this one a book matching multiple conditions would only be included in the first condition's count.

However, if the book_loans table is VERY large, you might still consider including the OR conditions in the WHERE clause.

Better way to get counts of data in this query?

3 Answers3