Better way to get counts of data in this query? [duplicate]

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.

juergen d · Accepted Answer · 2018-07-06 19:14:41Z

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 6, 2018 at 19:14

juergen d

205k40 gold badges304 silver badges377 bronze badges

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

1 Comment

Aran Bins Over a year ago

Thank you so much!

Brad · Accepted Answer · 2018-07-06 19:15:33Z

1

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
)

answered Jul 6, 2018 at 19:15

Brad

3,7253 gold badges32 silver badges54 bronze badges

Comments

Uueerdo · Accepted Answer · 2018-07-06 19:19:59Z

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.

Collectives™ on Stack Overflow

Better way to get counts of data in this query? [duplicate]

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related