Using two COUNTs in a query - a more elegant solution

[wpb]

Hello,

 

I have two tables in my database, a table of books, 'bookinfo' with book_ids and a table of reviews, 'reviews' with review_ids.

 

Each row in the review table references a particular book in the book table by book_id. There is also a 'boolean' enum ('Y', 'N') that specifies if a review is official or not.

 

I want to perform a single query that returns one row for every book in the book table, and counts both the total reviews and the official reviews.

 

So far I have achieved this as follows:

 

SELECT book_title,

  SUM(CASE WHEN bookinfo.book_id = reviews.book_id AND reviews.official="Y" THEN 1 ELSE 0 END) AS num_official_reviews,

  SUM(CASE WHEN bookinfo.book_id = reviews.book_id THEN 1 ELSE 0 END) AS num_reviews

FROM bookinfo, reviews

GROUP BY bookinfo.game_id

 

So this works, but doesn't seem very elegant to me.

 

I can achieve the results in two queries using a JOIN which is slightly nicer:

 

SELECT book_title,

  COUNT(reviews.review_id) AS num_public_reviews

FROM bookinfo LEFT JOIN reviews ON bookinfo.book_id = reviews.book_id AND reviews.official="Y"

GROUP BY bookinfo.book_id

 

and

 

SELECT book_title,

  COUNT(reviews.review_id) AS num_reviews

FROM bookinfo LEFT JOIN reviews ON bookinfo.book_id = reviews.book_id

GROUP BY bookinfo.book_id

 

So I was wondering if there is a better way to do this, perhaps using the above JOIN construct in a single query? Is that possible?

 

Many thanks,

 

[mwasif]

What about this?

SELECT book_title,
  SUM(CASE WHEN bookinfo.book_id = reviews.book_id AND reviews.official="Y" THEN 1 ELSE 0 END) AS num_official_reviews, COUNT(*) AS num_reviews
FROM bookinfo
LEFT JOIN reviews ON bookinfo.book_id = reviews.book_id
GROUP BY bookinfo.game_id

 

I have not tested the query.

[Barand]

As you are joining on the condition "bookinfo.book_id = reviews.book_id" then inclusion in the CASE is superfluous

 

SELECT book_title,
  SUM(CASE WHEN reviews.official="Y" THEN 1 ELSE 0 END) AS num_official_reviews, 
  COUNT(*) AS num_reviews
FROM bookinfo
LEFT JOIN reviews ON bookinfo.book_id = reviews.book_id
GROUP BY bookinfo.game_id