Subject Doubt Regarding Sum() Function
Author Vishal Tiwari
Hi All,
I have a table as follows:
Table Name : Book_Master

Book_Name  Book_Price
ABC              1250.00
LMN              5698.00
PQR             1524.00

Table Name : Book_Details

YearMonth   Sold_On_Date   Book_Name   Copy_Sold
200904          01/04/2009         ABC               565
200904          05/04/2009         PQR               986
200904          05/04/2009         LMN               120
200904          11/04/2009         ABC               565
200904          25/04/2009         PQR               986
200904          15/04/2009         LMN               120

Select BM.Book_Name, Sum(Book_Price) As Book_Price, Sum(COPY_SOLD) As COPY_SOLD
from Book_Master BM Join Book_Details BD On BM.Book_Name = BD.Book_Name
Where BD.YearMonth = '200904' Group By BM.Book_Name
I get the out put as:
Book_Name   Book_Price   Copy_Sold
ABC     2500.00  1130
LMN     11396.00  240
PQR     3048.00  1972

I thought that "Book_Price" column value  will be displayed as it is available in the "Book_Master" table and the records will be displayed only for those books which are having entries in "Book_Details" too.
But it is comming as Book_Price * No. of records exist in Book_Detauils table.
If I would like to use the Sum() function for Book_Price too then how should I use, one way is Sum(Distinct(EM.Book_Price)) but in future in some cases where I need to have multiple records in two different table I may wish to get sum of perticular column from one table and some of perticular column from other table using join, then that time I would get wrong result.
Here I would like to ask one question is there other way to achieve this or I have missed out something in above sql.
Thanks in advance.
With Best Regards.

[Non-text portions of this message have been removed]