Thread: How to obtain algebraic sum of equal lines

How to obtain algebraic sum of equal lines

From
Ennio-Sr
Date:
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) ]

Re: How to obtain algebraic sum of equal lines

From
Frank Bax
Date:
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"



Re: How to obtain algebraic sum of equal lines

From
Oisin Glynn
Date:
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


Re: How to obtain algebraic sum of equal lines

From
Ennio-Sr
Date:
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) ]

Re: How to obtain algebraic sum of equal lines

From
Oisin Glynn
Date:
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


Re: How to obtain algebraic sum of equal lines

From
Frank Bax
Date:
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.


Re: How to obtain algebraic sum of equal lines

From
Ennio-Sr
Date:
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) ]

Re: How to obtain algebraic sum of equal lines

From
Ennio-Sr
Date:
* 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) ]

Re: How to obtain algebraic sum of equal lines

From
Oscar Rodriguez Fonseca
Date:
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