Hi Daniel Rail,
 
I have done some correction in your sql i.e. field name was not put correctly, and in second sql I used Full Outer Join to get exact details.
 
SQL 1):
 
Select BM.Book_Name,
BM.Book_Price,
BD.COPY_SOLD
from (select Book_Name, Sum(Book_Price) as Book_Price
from Book_Master
group by Book_Name) BM
Join (select Book_Name, Sum(COPY_SOLD) as COPY_SOLD
from Book_Details
where Book_Details.YearMonth = '200904'
group by Book_Name) BD
On BM.Book_Name = BD.Book_Name
Order by BM.Book_Name


SQL 2);
 
Select MB.Book_Name, MB_Copy_Sold, SB_Copy_Sold
from
(
Select Book_Name, Coalesce(Sum(Copy_Sold), 0) As MB_Copy_Sold from MAIN_BRANCH_BOOK_DETAILS Group By Book_Name
) MB
Full outer join
(
Select Book_Name, Coalesce(Sum(Copy_Sold), 0) As SB_Copy_Sold from SUB_BRANCH_BOOK_DETAILS Group By Book_Name
) SB
On
  MB.Book_Name = SB.Book_Name
Order By MB.Book_Name
 
 
A Heartful Thank You.
 
Today I learnt one more way to write sql.
 
Thank You.
 
 
With Best Regards.
 
Vishal

--- On Wed, 15/9/10, Daniel Rail <daniel@...> wrote:


From: Daniel Rail <daniel@...>
Subject: Re: [firebird-support] Doubt Regarding Sum() Function
To: firebird-support@yahoogroups.com
Date: Wednesday, 15 September, 2010, 5:03 PM


 



Hi,

At September-15-10, 1:01 AM, Vishal Tiwari wrote:

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

The above result is expected given the query.

>  
> But Required result is as follows:
>  
> Book_Name   Book_Price   Copy_Sold
> ABC               1250.00          1130
> LMN               5698.00          240
> PQR               1524.00          1972

This would require that the query would be written differently, as I
already mentioned in another thread.

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

As expected. And, if you would run the query without the SUM and
GROUP BY, as I have it below:

Select BM.Book_Name, Book_Price, 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

You will see the result set that would be used to calculate the SUM
columns of your query.

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

What you want would be to use derived tables(essentially subqueries in
the FROM clause), but this is only available in Firebird 2+.

i.e.:
Select BM.Book_Name,
BM.Book_Price,
BD.COPY_SOLD
from (select Book_Name, Sum(Book_Price) as Book_Price
from Book_Master
group by BookName) BM
Join (select Book_Name, Sum(COPY_SOLD) as COPYSOLD
from Book_Details
where Book_Details.YearMonth = '200904'
group by Book_Name) BD
On BM.Book_Name = BD.Book_Name
Order by BM_Book_Name

The other option would be to use an EXECUTE BLOCK statement, you'll
find the information in the release notes.

--
Best regards,
Daniel Rail
Senior Software Developer
ACCRA Solutions Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)










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