Select same column value with count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select same column value with count

Author

Topic

sushant17284
Starting Member

12 Posts

Posted - 2009-10-08 : 02:29:57

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

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-08 : 02:34:10

select book_id, count(book_id) as Times_Transacted from
Transaction group by book_id

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

sushant17284
Starting Member

12 Posts

Posted - 2009-10-08 : 02:39:03

Thanks Senthil,
You resolved it Mate !!!!!!
Cheers .....Great Stuff

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-08 : 02:46:23

quote:
Originally posted by sushant17284

Thanks Senthil,
You resolved it Mate !!!!!!
Cheers .....Great Stuff

Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources