Subject Re: [firebird-support] Re: question about joins
Author Michael Weissenbacher
>> For this case it would be possible to use something like:
>> select, sum(s.sales)/3 salessum, sum(c.costs)/3 costssum
>>from maintable m
> My example was an extremely simplified statement. In reality it's much
> longer, and needs to run without my having to divide by 3 or another
> constant. Your solution here would require that I knew in advance the
> number of records found in each table per ID which isn't feasable.
I know that it was just an example. But your assumption that you need to
know the number of records in advance is wrong. AFAIK the divisor
depends on the number of tables joined.
for 2: 1
for 3: 3
for 4: 6
for 5: 10
for 6: 15
for n: (n-1)*n/2

>> I think subselects, as shown by Jose Ostos and Paul Mercea, would be
> the
>> normal way to solve things like this. May I ask you why that is not an
>> alternative? Alternatives that I can think of are adding views or
> stored
>> procedures that does the sum for you, but such solutions sounds
> inferior
>> to simple subselects.
> It's hard to explain without pages of info (Which I can send if you'd
> really want!). It actually is a stored procedure. I'm guessing this
> isn't possible, but I was hoping there was some trick I was missing.
Well, i'd go for subselects if possible, for readability and
performance. IMO there is nothing that could prevent you from doing
subselects in a stored procedure.