Hi,
 
 
Select BM.Book_Name, Max(BM.Book_Price), ...
this would not give me the sum.
 
i am talking about the feature case where i need to have multiple records exist in two tables and I need to have sum of respective column from respective table only.
 
Vishal

--- On Tue, 14/9/10, Tomas Krejzek <tomas.krejzek@...> wrote:


From: Tomas Krejzek <tomas.krejzek@...>
Subject: Re: [firebird-support] Doubt Regarding Sum() Function
To: firebird-support@yahoogroups.com
Date: Tuesday, 14 September, 2010, 5:08 PM


 



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

This is correct result in SQL standard because it joins to tables and then it sum 2 columns

You have two posibilities how to get You want

Select BM.Book_Name, BM.Book_Price, .... Group By BM.Book_Name, BM.Book_Price;

or

Select BM.Book_Name, Max(BM.Book_Price), ...

to be it exactly clear the first one is better

Bye Tom









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