Re: How to obtain algebraic sum of equal lines

At 05:21 PM 4/25/06, Ennio-Sr wrote:

>Hi Oisin and Frank,
>
>* Frank Bax <fbax(at)sympatico(dot)ca> [250406, 16:41]:
> > At 04:12 PM 4/25/06, Ennio-Sr wrote:
> >
> > >Hi all,
> > >Is it possible, given the following table:
> > >
> > >cod_rif | titolo | quantity | cmf | u_qq | mont | vend | sum
> > >---------+--------+----------+---------+--------+---------+------+-------
> > > 26 | aaa | -1000 | 6.11098 | 6.176 | 6.1110 | t | -1000
> > > 7 | aaa | 2500 | 25.455 | 0 | 60.0897 | f | 2500
> > > 28 | bbb | 2700 | 3.862 | 4.6 | 4.1957 | f | 2700
> > > 3 | bbb | 500 | 6.057 | 10.129 | 18.7311 | f | 500
> > > 34 | bbb | -700 | 0 | 0 | 0.0000 | t | -700
> > > 6 | bbb | -500 | 2.703 | 4.757 | 3.7151 | f | -500
> > > 30 | ccc | 5000 | 1.717 | 1.489 | 1.7170 | f | 5000
> > > 33 | ccc | 10000 | 4.36 | 0 | 4.3600 | f | 10000
> > > 11 | ddd | -1500 | 10.537 | 4.021 | 20.5815 | t | -1500
> > > 32 | ddd | 1500 | 0.55896 | 1.119 | 0.5590 | f | 1500
> > >(10 rows)
> > >
> > >to get a selection whereby the algebraic sum of "quantity" for each equal
> > >'titolo'
> > >is returned?
> > >
> > >I tried this query with no result:
> > >psql finanza -c "select * , (sum(quantity)) from test_t group by
> cod_rif,
> > >titolo, quantity, cmf, u_qq, mont, vend order by titolo asc"
> >
> > psql finanza -c "select titilo, (sum(quantity)) from test_t group by
> > titolo order by titolo asc"
> >
>
>The point is that command would return two cols only, whereas I would
>like to have all the cols, like:
>
> 26 | aaa | 1500 | 6.11098 | 6.176 | 6.1110 | t | 1500
> 28 | bbb | 2000 | 3.862 | 4.6 | 4.1957 | f | 3200
>
>i.e. aaa=(-1000+2500=1500), bbb=(2700+500-700-500)=2000
>which, I fear, is not possible ;-(

It might be possible, but only if you can provide a rule for choosing
values for the other columns. For example, when titolo='aaa', then cod_rif
could be 26 or 7; you chose 26 for your sample output - why is 7 not the
"right" result for this column.