Thread: How to obtain algebraic sum of equal lines
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
orderby titolo asc"
Regards,
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) ]
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"
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 orderby titolo asc" > > Regards, > Ennio. > > > Perhaps I am not seeing the light but would the following not give sum per titolo? select sum(quantity),titolo from test_t group by titolo; Oisin
Hi Oisin and Frank, * Frank Bax <fbax@sympatico.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 ;-( -- [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) ]
Ennio-Sr wrote: > Hi Oisin and Frank, > > * Frank Bax <fbax@sympatico.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 ;-( > > select *,(select sum(quantity) from test_t t2 where t2.titolo=t1.titolo) as sum from test_t t1; would do it though who knows how inefficient this is? Oisin
At 05:21 PM 4/25/06, Ennio-Sr wrote: >Hi Oisin and Frank, > >* Frank Bax <fbax@sympatico.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.
Hi Frank,
* Frank Bax <fbax@sympatico.ca> [250406, 17:25]:
> At 05:21 PM 4/25/06, Ennio-Sr wrote:
>
> >Hi Oisin and Frank,
> >
> >* Frank Bax <fbax@sympatico.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) ]
* Oisin Glynn <me@oisinglynn.com> [250406, 17:24]:
> Ennio-Sr wrote:
> >Hi Oisin and Frank,
> >
> >* Frank Bax <fbax@sympatico.ca> [250406, 16:41]:
> >
> >>At 04:12 PM 4/25/06, Ennio-Sr wrote:
> >>
> >>
> >>>Hi all,
> >>>Is it possible, given the following table:
> >>> ...
> >
> >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 ;-(
> >
> >
> select *,(select sum(quantity) from test_t t2 where t2.titolo=t1.titolo)
> as sum from test_t t1;
>
> would do it though who knows how inefficient this is?
>
Well, it's better than nothing, anyway. At list I'll get the balance
quantity for each equal titolo :-)
Thank you Oisin!
Regards,
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) ]
On Tue, 25 Apr 2006 22:12:40 +0200 Ennio-Sr <nasr.laili@tin.it> wrote: > 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 [...] > (10 rows) > to get a selection whereby the algebraic sum of "quantity" for each equal 'titolo' > is returned? I am no expert but maybe you could try using "join": SELECT t.*,s.sum AS quantity_sum FROM test_t AS t \ INNER JOIN (SELECT titolo,sum(quantity) FROM test_t GROUP BY titolo) AS s \ ON t.titolo = s.titolo ORDER BY t.titolo ASC This (untested) command will just add a column quantity_sum to the result table. Regards, -- Oscar