Yabingooglehoo helps here a lot. See the following URL.

https://msdn.microsoft.com/en-us/library/ms190307.aspx

Example "B" is similar to solving your problem as I did in the code below. You can make things much more pretty by taking a look at example "C".

Here's the simple code:

SELECT TitleID, COUNT(*) cnt

INTO ##Results

FROM dbo.BookOrderTest

GROUP BY TitleID

HAVING COUNT(*)>1

;

IF @@ROWCOUNT > 0

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'put email address(es) here',

@query = 'SET NOCOUNT ON; SELECT * FROM ##Results' ,

@subject = 'Duplicate Book Orders',

@body = 'Put any amplifying information you want to send here.'

;