--- In firebird-support@yahoogroups.com, Maurizio <mauriz_po@...> wrote:

>
> hi , i am try to make a select from 2 tables something like this :
>
> SELECT F.frdescr1 AS "Fornitore " , SUM(DM.mrqtamov ) AS "Qtà

Acquisto"

> , 0 as "Qtà Resa "
> FROM tmovmagamaster M
> JOIN tmovmagadetail DM ON (M.mmidriga = DM.mr____fk )
> JOIN tcaumag CM ON (CM.cmcodcau = M.mmcaumag )
> JOIN tfornitori F ON (F.frcodfor = M.mmcodfor )
> WHERE CM.cmcommov = 'P'
> GROUP BY F.frdescr1
>
> UNION ALL
>
> SELECT F2.frdescr1 AS "Fornitore " , 0 AS "Qtà Acquisto" ,
> SUM(DD.dtqtamov ) as "Qtà Resa "
> FROM TDOCMASTER MV
> JOIN tdocdetail DD ON (MV.dm__key1 = DD.dt_kmast )
> join tcaudocumenti CD on (CD.cdcodice = MV.dmcodcau )
> JOIN tfornitori F2 ON (F2.frcodfor = MV.dmcodfor )
> WHERE CD.cdcodice = 'REF'
> GROUP BY F2.frdescr1
>
> 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.

Adam