Skip site navigation (1) Skip section navigation (2)

Re: How to obtain algebraic sum of equal lines

From: Oisin Glynn <me(at)oisinglynn(dot)com>
To: nasr(dot)laili(at)tin(dot)it, pgsql-novice(at)postgresql(dot)org
Subject: Re: How to obtain algebraic sum of equal lines
Date: 2006-04-25 21:24:58
Message-ID: 444E93AA.7040809@oisinglynn.com (view raw or flat)
Thread:
Lists: pgsql-novice
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 ;-(
>
>   
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


In response to

Responses

pgsql-novice by date

Next:From: Frank BaxDate: 2006-04-25 21:25:02
Subject: Re: How to obtain algebraic sum of equal lines
Previous:From: Ennio-SrDate: 2006-04-25 21:21:22
Subject: Re: How to obtain algebraic sum of equal lines

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group