Subject Doubt Regarding Sum() Function Vishal Tiwari 2010-09-15T04:01:07Z
Hi All,

I am putting my doubt again, sorry for that.

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

SQL:
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

But Required result is as follows:

Book_Name   Book_Price   Copy_Sold
ABC               1250.00          1130
LMN               5698.00          240
PQR               1524.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.