SQL CASE STATEMENT in COUNT CLAUSE

Question

I'm trying to get a product's name and its number of sales from two separate tables.

My tables look something like this:

BOOK
Book_ID | Book_Title | Book_Author  

SOLD  
Transaction_ID | Book_ID | Customer_ID

I can get most of the results I want from the following query

SELECT b.Book_Title, COUNT(s.Book_ID) FROM Book b, Sold s 
WHERE b.Book_ID = s.Book_ID 
GROUP BY b.Book_Title;

However, this only displays products with at least one sale. I would like to display all products, simply showing a zero if no sales have occurred. I've been messing around with something like this:

SELECT b.Book_Title, 
       COUNT(CASE WHEN s.Book_ID IS NULL THEN 0 ELSE s.Book_ID END) 
FROM Book b, Sold s WHERE b.Book_ID = s.Book_ID GROUP BY Book_Title;

But the WHERE clause is limiting the results to the ones with 1 or more sales.

Can anyone suggest a way around this? I am using Oracle 10g.

Thanks

Answer 1

use a left outer join:

SELECT b.Book_Title, COUNT(s.Book_ID) 
FROM Book b left outer join Sold s on b.Book_ID = s.Book_ID 
GROUP BY b.Book_Title;

Answer 2

make an other join from book to sold. you may still get a null for the count, but you resolve that by adding a NVL on top of that...

Answer 3

As @Vincent said, you need an outer join. I haven't worked much with Oracle lately, but its proprietary outer join syntax is rather bizarre. (I don't know whether they've caught up with ANSI on that.)

The proprietary syntax is:

  SELECT b.Book_Title,
         COUNT(s.Book_ID)
    FROM Book b,
         Sold s
   WHERE b.Book_ID = s.Book_ID (+)
GROUP BY b.Book_Title;

Answer 4

You should get the count in a subquery and left outer join to it as such:

select b.book_title, 
   case when s.book_id is null then 0 
       else s.salesCount end as Sales
from book b
left outer join 
  (select count(*) as salesCount, book_id from sales group by book_id) s on b.book_id = s.book_id

Answer 5

You can also use a correlated subquery in the select clause :

select b.book_title, (select count(*) from sold s where s.book_id=b.book_id) from book b

It doesn't need either group by or outer joins, which can be slow for very large number of rows.