>Try the following to see whether it gets the set you are looking for:
>
>select
> pat.cfg_tipo,
> pat.cfg_codice,
> sum(pat.importo_dovuto) s_importo_dovuto,
> coalesce(
> sum(pas.importo_pagato), 0) s_importo_pagato
> from pat
> left join pas
> on pas.progressivo = pat.progressivo
>where pat.cfg_tipo = 'C'
>group by 1,2

Hmm, I wonder whether 'coalesce(sum(...' means that he also has to group by 4? I don't think I've ever tried such a construct, though I may have done 'sum(coalesce(...'. However, that just puzzles me and not something that I could easily have found the answer to by writing a simple query myself, and Fabio will know the answer as soon as he tries to execute the query.

What's far more important, is that the above query probably produce a completely different result from what Fabio actually wants. His original statement might not work, but I think the 'fixed statement' doubles s_importe_dovuto if there are two rows with a given pas.progressivo, triples it if there are three and so on...

So I'd rather try something like:

with p (progressivo, importo_pagato) as
(SELECT progressivo, sum(importo_pagato)
from pas
group by 1)

select pat.cfg_tipo, pat.cfg_codice, sum(pat.importo_dovuto) importo_dovuto,
coalesce(p.importo_pagato, 0) importo_pagato
from pat
left join p on p.progressivo = pat.progressivo
where pat.cfg_tipo = 'C'
group by 1, 2, 4

Other ways to solve this (I think, I'm not 100% certain), would be to use 'group by 1, 2, 4' on the original statement or introduce another aggregate function (COALESCE isn't an aggregate function and my hunch is that is the reason Fabio gets the error in the first place):

select pat.cfg_tipo, pat.cfg_codice, sum(pat.importo_dovuto) importo_dovuto,
min(coalesce((select sum(importo_pagato)
from pas where pas.progressivo = pat.progressivo), 0)) importo_pagato
from pat where pat.cfg_tipo = 'C'
group by 1, 2

However, I think that using WITH is the cleanest way to write this SQL.

HTH,
Set