Hi PenWin,
 
Thank You for your kind reply.
 
I think I faild to represent my doubt, but give me one more chance, please find my doubt as follows:
 
First of all, I got the surprisable result the way I wrote my first sql.
I agree I would have written the same sql by others said, I agree.
 
Ya obviously, others gave me the correct sql and which is also working fine, no doubt in that, I agree with this also.
 
But at the same time, I also didn't forget to ask my main doubt in the previous mail that, "In future in some cases where I need to have multiple records in two different tables 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 the way I wrothe my first sql."
 
For this we would consider an example that, I have two tables with the following same table structure, named "Main_Branch_Book_Details" and "Sub_Branch_Book_Details", now with this table structure and the data availability, if I need to find the how many copies sold for each book for the specified YearMonth in the Main Branch (i.e. "Main_Branch_Book_Details" table) as well as in the Sub Branch (i.e. "Sub_Branch_Book_Details" table) using single sql.
 
"Main_Branch_Book_Details" table data is as follows:
 
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

 
"Sub_Branch_Book_Details" table data is as follows:
 
YearMonth  Sold_On_Date   Book_Name   Copy_Sold 
200904       01.04.2009        ABC               465
200904       05.04.2009        PQR               886
200904       05.04.2009        LMN               720
200904       11.04.2009        ABC               885
200904       25.04.2009        PQR               478
200904       15.04.2009        LMN               420

 
Now I was talking about this above situation, now how do I achieve, how many copies are sold for the specified YearMonth in Man Branch as well as In Sub Branch.
 
I hope I succeeded to represent my doubt.
 
 
Thanks In Advance.
 
With Best Regards.
 
Vishal

--- On Wed, 15/9/10, PenWin <penwin@...> wrote:


From: PenWin <penwin@...>
Subject: Re: [firebird-support] Doubt Regarding Sum() Function
To: firebird-support@yahoogroups.com
Date: Wednesday, 15 September, 2010, 10:46 AM


 



> 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

Someone has already told you that you should either add Book_Price to
GROUP BY (usually a better solution) or use MAX(Book_Price). Is there
any reason why you persist in using SUM which is completely unsuitable
for the task you want?

> 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.

Well, DUH! You are telling SQL to sum all Book_Price's for a particular
book. Since your recordset after joingin and before group operations
looks something like this:

ABC 1250.00 123
ABC 1250.00 456

Obviously the final displayed price will be Book_Price * Number of rows
for this book in Book_Details.

> If I would like to use the Sum() function for Book_Price too then how

First answer this: WHY do you want to use sum? Either you want a price
per book and then SUM is clearly unsuitable, or you want a total
earnings from books and then you should use SUM(Book_Price*Copy_Sold).

Pepak









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