Hi,
 
left Outer Join is not working with required values.
]
Vishal

--- On Tue, 14/9/10, Aurimas Černius <aurimas@...> wrote:


From: Aurimas Černius <aurimas@...>
Subject: Re: [firebird-support] Doubt Regarding Sum() Function
To: firebird-support@yahoogroups.com
Date: Tuesday, 14 September, 2010, 4:56 PM


 



Hi,

My mistake in previous e-mail, sorry...

> 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

You need to group by ALL columns you want to display "as is", in case
you missed that.

> I get the out put as:
>
>
> Book_Name Book_Price Copy_Sold
> ABC 2500.00 1130
> LMN 11396.00 240
> PQR 3048.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.

Use LEFT JOIN, if you want all rows from first table, no matter what is
in the other one.

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

Sometimes you need to do select * from (select_statement_here). Ordinary
group by not always does the job.

--
Aurimas









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