Hi Mark,
 
Pl do not go on database design (I know it is not good one, I have taken this example to just know that how to achieve the two columns summation independantely), it is just an example to understand my doubt.
 
In general I would like to know that, if I need to do summation of two different columns from two different tables (provided database is designed well) independentely in the same sql, that time how I should achieve that, as you said I should use UNION as per the sql requirement.
 
I just want to get the accurate sum, if I come accross with some situation then how should I do this?
 
 
Thanks for the reply.
 
 
With Best Regards.
 
Vishal
 

--- On Wed, 15/9/10, Mark Rotteveel <Avalanche1979@...> wrote:


From: Mark Rotteveel <Avalanche1979@...>
Subject: Re: [firebird-support] Doubt Regarding Sum() Function
To: firebird-support@yahoogroups.com, firebird-support@yahoogroups.com
Date: Wednesday, 15 September, 2010, 1:33 PM


 




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

It is not a surprising result, it is the expected result for that query. In a query where you use join, you are essentially creating a temporary table that is used to perform the rest of the query.

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

Then you should probably not be using join, but union (or maybe union in combination with a more specific join)

> 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
<snip>
>  
> "Sub_Branch_Book_Details" table data is as follows:
>  
> YearMonth  Sold_On_Date   Book_Name   Copy_Sold 
> 200904       01.04.2009       
> ABC               465
<snip>

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

First of all, that is a bad database design. The data of the main and sub branch should be in the same table, because they represent the same kind of entities. You would then distinguish between data from the main and sub branch using a foreign key to (for example) a branch table. You would then group by the name of the book and the name of the branch.

But if this data structure is a given, than you should use union, not join to get the data.

Mark
--
Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief!
Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail









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