Select same column value and count

[sushant17284]

Guys,
I need your help as I really can't seem to start on this query.I seem to not be able to come up with any logic.

I have to create a query for a table.
I have two tables in my DB .
First one is a book table which consists of information about a book
My table looks like following

Table Book
------------------------------------------------------------
book_id | book_title | book_author
-------------------------------------------------------------
1 | Abc | Xyz
2 | Def | Xyz
3 | Ghi | Xyz

The other table is the transaction table which is used to issue books to a user

Table Transaction
------------------------------------------------------------
transaction_id | book_id | transaction_date
-------------------------------------------------------------
1 | 2 | XX-XX-XXXX
2 | 1 | XX-XX-XXXX
3 | 1 | XX-XX-XXXX
4 | 3 | XX-XX-XXXX
5 | 2 | XX-XX-XXXX
6 | 2 | XX-XX-XXXX
7 | 1 | XX-XX-XXXX
8 | 2 | XX-XX-XXXX

Now the query which I need to make is to get the results of top issued books which means to display the books which have been issued the most or in other words been transacted the most (along the with the amount of times the book has been transacted).
So that the result look like this
----------------------------------------------------
Book_id | Times_Transacted
-----------------------------------------------------
2 | 4
1 | 3
3 | 1

I would really appreciate any help on this.
Cheers,
Sushant

[sknake]

IF OBJECT_ID('BookTest', 'U') IS NOT NULL DROP Table BookTest
IF OBJECT_ID('TxnTest', 'U') IS NOT NULL DROP Table TxnTest
GO
CREATE TABLE BookTest
(
  book_id int,
  book_title varchar(3),
  book_author varchar(3)
)
Create Table TxnTest
(
  transaction_id int,
  book_id int,
  transaction_date varchar(10)
)
GO
Insert Into BookTest Values (1, 'Abc', 'Xyz')
Insert Into BookTest Values (2, 'Def', 'Xyz')
Insert Into BookTest Values (3, 'Ghi', 'Xyz')
Insert Into TxnTest Values (1, 2, 'XX-XX-XXXX')
Insert Into TxnTest Values (2, 1, 'XX-XX-XXXX')
Insert Into TxnTest Values (3, 1, 'XX-XX-XXXX')
Insert Into TxnTest Values (4, 3, 'XX-XX-XXXX')
Insert Into TxnTest Values (5, 2, 'XX-XX-XXXX')
Insert Into TxnTest Values (6, 2, 'XX-XX-XXXX')
Insert Into TxnTest Values (7, 1, 'XX-XX-XXXX')
Insert Into TxnTest Values (8, 2, 'XX-XX-XXXX')
GO

Select Top 10 book_id,
(
  Select Count(*)
  From TxnTest (NOLOCK)
  Where TxnTest.book_id = BookTest.book_id
) As Cnt
From BookTest
Order By Cnt Desc

[sushant17284]

Thanks Mate,
I really appreciate your effort for resolving this problem.
Cheers,
Sushant

[sknake]

You're welcome

Please mark this thread as solved if you have found an answer to your question and good luck!