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
>