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)