Hi Vishal!

Aggregate functions (e.g. SUM) works on the result set, not individual parts of the result set. Hence, you risk both Book_Price and Copy_Sold to be multiples of what you probably want. If you want SUM to operate on only parts of your result set, then treat is a separate parts:

WITH BM (Book_Name, Book_Price) as
(SELECT Book_Name, sum(Book_Price)
FROM Book_Master
GROUP BY Book_Name),
BD(Book_Name, Copy_Sold) as
(SELECT Book_Name, sum(Copy_Sold)
FROM Book_Detail
WHERE YearMonth = '200904'
GROUP BY Book_Name)

SELECT BM.Book_Name, BM.Book_Price, BD.Copy_Sold
FROM BM
JOIN BD ON BM.Book_Name = BD.Book_Name

Now, each WITH part makes Book_Name singular, so it would have been enough to have one WITH and leave the other in the main SELECT, but the way I chose to do it clearer shows that you are trying to answer two independent questions within one query.

HTH,
Set