Re: How to obtain algebraic sum of equal lines

[Ennio-Sr <nasr(dot)laili(at)tin(dot)it>]

Hi Frank,
* Frank Bax <fbax(at)sympatico(dot)ca> [250406, 17:25]:
> 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?
> >> >
..............
> >
> >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. 
> 

The problem, as I see it, is that each line for the same titolo has
many different values (not only cod_rif, but also other cols differs)
and (probably) PG doesn't know how to sum 'potatoes'+'tomatoes'...
I think I could drop cod_ref in the select, if that helped, not the
other columns.

Oisin's suggestion helps a bit, however.
Thanks,
	Ennio






-- 
[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) ]