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

Re: Select same column value and count 80 80
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
Re: Select same column value and count 80 80

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

Re: Select same column value and count 80 80

You're welcome

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.