MySQL COUNT value as a column

Question

I have two tables

BOOK(book_id, book_name)

and

COPY(copy_id, book_id, available(bool))

I want to have a query to display book_id, book_name, and COUNT(*) of all copies with the same book_id in COPY table that have available set to true. Sample query output should look like this:

1, BookA, 2
2, BookB, 4
3, BookC, 0

So BookA has two COPY tuples with available set to true. This is what I have so far, it doesn't work.

SELECT * FROM
(SELECT * FROM BOOK)book,
(SELECT book_id, COUNT(copy_id) FROM COPY)copy
WHERE book.book_id = copy.book_id;

juergen d · Accepted Answer · 2012-12-01 10:20:09Z

2

Since bool values are 1 if true in MySQL, you can sum them up

SELECT b.book_id, 
       b.book_name, 
       sum(c.available) as copy_count
FROM book b
left outer join copy c on c.book_id = b.book_id
group by b.book_id

SQLFiddle demo

edited Dec 1, 2012 at 10:20

answered Dec 1, 2012 at 10:03

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

5 Comments

Michael Over a year ago

Good query for the case that copy id is an ID and it is unique.

user1795609 Over a year ago

This one doesn't display my third case in which BookC has 0 COUNT of available.

Ilion Over a year ago

try changing sum(c.available) to sum(ifnull(c.available,0)). (although @juergend seems to be right)

Ilion Over a year ago

Inner joins only display rows when they match in both tables. Outer joins will always display from one regardless of a match in the other.

juergen d Over a year ago

@user1795609: See this very good join explanation

Barmar · Accepted Answer · 2012-12-01 10:32:12Z

0

Here's another variant:

SELECT b.book_id, 
       b.book_name, 
       count(c.available) as copy_count
FROM book b
left outer join copy c on c.book_id = b.book_id and c.available = 1
group by b.book_id

This is a little more efficient because the join doesn't include any of the rows with unavailable books, so it's a smaller intermediate table to process.

answered Dec 1, 2012 at 10:32

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

MySQL COUNT value as a column

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related