Re: How to get single raws for sums in a summary table?

* Richard Broersma Jr <rabroersma(at)yahoo(dot)com> [221106, 12:06]:
> >
> > Hi Richard (and others),
> >
> > I was cheering too early in my previous answer ;)
> > The query is only apparently giving a sum: in effect the value in each
> > raw is just one of the values of raws having the same 'titolo'.
> >
> > What I had in mind was the possibility to achieve the same result I used
> > to get (quite a few years back) in DBIII, when printing data and setting
> > subtotals for same items. But this is history ......
> >
>
> This should would a little better.
>
> select
> distinct on (titolo)
> ^^^^^^
> 'any' as cod_rif,
> titolo,
> (
> select A1.sum(quantity) from test_t as A1
> where A1.titolo = test_t.titolo
> ) as sum_qty,
> cmf,
> u_qq,
> mont,
> vend,
> from
> test_t
> group by
> titolo,
> sum_qty,
> cmf,
> u_qq,
> mont,
> vend
> ;
>
>
> Regards,
> Richard Broersma Jr.

Hi Richard,
this seems to be the right one (with a few minor corrections - see
below).
It works greatly on my test_t (I haven't tested it on the real table yet).
Thanks again for your help.
Regards,
Ennio.

------- this is ok ----------------
select
distinct on (titolo)
'any' as cod_rif,
titolo,
(select sum(quantity) from test_t A1
^
where A1.titolo = test_t.titolo) as "q_res",
cmf, ^^^^^^^
u_qq,
mont,
vend
^
from
test_t
group by
titolo,
quantity,
-- sum_qty,
cmf,
u_qq,
mont,
vend
;
-----------------------------------

PS: I didn't go through all pg-documentation so my guess might be a bit
of hazard: shouldn't the 'distinct on (some_field)' do all the job on its own
with no need for the second (sub)select? Otherwise there seems to be no
difference between 'select distinct on (fx) f1, f2, .,fx,. ,fn' and
'select distinct f1, f2, .,fx,. fn'.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]