sql query to get no of books n no of available books

I need a query which will return list of book from book table bit it will show total number of books and books available too .. N for number of books it will count number of books with simular ISBN n amount count which will count number of books with simular ISBN with status available Table structure Bookid booktitle author ISBN edition publisher price supplier volume language status status contains available,issued I want result as Book Name quantity available Please also explain the query because I need to implement it on many tables