Count Duplication

create table books
(
 id int identity(1,1),
 custname varchar(25),
 bookname varchar(25)
)

INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'naimish','five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'ravi', 'five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'jugal','five point someone')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'jugal','3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'naimish','3 mistakes of my life ')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'nikhi', 'satya na prayogo')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'ram', 'ravan')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'misi','evil')
INSERT [dbo].[books] ( [custname], [bookname]) VALUES ( 'michel','evil')

Ans :-


SELECT bookname,COUNT(*) NoOfbooks
FROM books
GROUP BY bookname
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC