You still haven't told us very much, Maurizio, but enough for me to have
a guess.

When you say "it does not function", what you're really saying is not 'I
get an error' (if I'm wrong, then please tell us the error message -
preferably translated to English if it is in another language) as almost
all on this list believes, but rather that the result is different from
what you want.

I created the two tables in Firebird 2.5 Alpha, then ran

with ta as
(select cola1, sum(cola2) total1
from tablea
group by 1),
tb as
(select colb1, sum(colb2) total2
from tableb
group by 1)
select coalesce(ta.cola1, tb.colb1) as col1,
coalesce(ta.total1, 0) as Total1,
coalesce(tb.total2, 0) as Total2
from ta
full join tb on ta.cola1 = tb.colb1

and got the result you probably want (assuming that you want results
also for col1-values only in one of the tables and that you want the
column for the other table for that col1 to return 0 rather than
<null>). I think the select above should also work on Firebird 2.1
(though I haven't tested), but not on older Firebird versions (you
forgot to tell us which version you run).

using UNION ALL will get you a dataset which contains all the rows from
both the first and second select, but they will always be on separate lines.

HTH,
Set

Maurizio P. wrote:

> ________________________________
> Da: Adam <s3057043@...>
> A: firebird-support@yahoogroups.com
> Inviato: Sabato 14 febbraio 2009, 2:33:09
> Oggetto: [firebird-support] Re: union all and group by
>
> ok , so i try to explain better :
> i have two tables
>
> tablea
>
> cola1 cola2
>
> for1 30
> for2 15
> for3 60
> for2 5
>
> tableb
>
> colb1 colb2
>
> for3 2
> for2 1
> for1 10
> for2 5
>
> result should be :
>
> col1 total1 total2
>
> for1 30 10
> for2 20 6
> for3 60 5
>
> where total1 is the sum of cola2 and total2 is the sum of colb2
> thanks
> Maurizio
>
>> it does not function .
>> what i want to do is to have the select grouped by the first column ,
>> globally .
>>
>> i just hope i have been clear .
>
> "Does not function" is not a particularly useful error report. Next
> time explain whether you received an error message (and provide it) or
> whether the results returned were otherwise incorrect.
>
> Looking at your SQL, I am guessing that you got an error message
> because it is invalid. EVERY term in your select that is not an
> aggregate must appear in the group by. In the first part of the query,
> field 3 is missing from the group by. In the second part of the query,
> field 2 is missing from the group by.=20
>
> Adam