Well, Firebird 1.5 doesn't support derived tables, so your original
selection is not an option. I'd say you're left with at least four
choices (all could be good), one of them I already outlined below, the
other options are writing a stored procedure, creating a view for
calculating the sum and then join to this as if it was a table, and
finally using a subselect within your main select like this:

select
v.vatcode,
(select
sum(netvalue)
from disbursements d
where v.transno = d.transnod) as disbs,
v.vatvalue,
v.netvalue
from vattransactions v
where v.vatquarter = 20
and v.inputoutput = 'O'
order by
v.vatcode

Myself, I rarely bother using views (they can be useful, I'm just not
using them) and write stored procedures only when simpler select
statements aren't sufficient. Hence, I would likely go for the first or
last option. The last option should be the most similar to what you
tried that doesn't work, so maybe that could be a good choice for you?

Whichever you choose, good luck.
Set

martinthrelly wrote:

> hello. i am using firebird 1.5. the reason that i did not opt to use
> the GROUP BY clause is that my proper sql actually contains about 10
> select statements and 5 other inner joins. i just cut it down for
> this example. so it felt wrong to be grouping it by all 10 criteria
> just to get one SUM column.
>
> what is the best way for me to do this sql do you think?
>
> --- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
> <svein.erling.tysvaer@...> wrote:
>> Assuming the combination vatcode, vatvalue and netvalue is unique,
> your
>> select can be simplified, making the derived table unneccessary:
>>
>> select
>> v.vatcode,
>> sum(d.netvalue) as disbs
>> v.vatvalue,
>> v.netvalue
>> from vattransactions v
>> join disbursements d on v.transno = d.transno
>> where v.vatquarter = 20
>> and v.inputoutput = 'O'
>> group by
>> v.vatcode, v.vatvalue, v.netvalue
>> order by
>> v.vatcode
>>
>> HTH,
>> Set
>>
>> martinthrelly wrote:
>>> hi i am trying to run the following sql within IBExpert but it
> wont
>>> run saying "invalid token Select". i realise this may be a
> simple
>>> error i am making but could somebody please point me out why i
> cannot
>>> join a summed amount like this. thanks.
>>>
>>> select
>>> v.vatcode,
>>> d.disbs,
>>> v.vatvalue,
>>> v.netvalue
>>> from vattransactions v
>>> inner join (
>>> select
>>> sum(netvalue) as disbs
>>> from disbursements) d on v.transno = d.transno
>>> where v.vatquarter = 20
>>> and v.inputoutput = 'O'
>>> order by
>>> v.vatcode