How to get single raws for sums in a summary table?

From: Ennio-Sr <nasr(dot)laili(at)tin(dot)it>
To: pgsql-novice(at)postgresql(dot)org
Subject: How to get single raws for sums in a summary table?
Date: 2006-11-22 00:50:05
Message-ID: 20061122005005.GA23196@deby.ei.hnet
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi all,

I would like to get a summary table from an original one having the
following structure:

Table "public.test_t"
Column | Type | Modifiers
----------+-----------------------+-----------
cod_rif | character(3) |
titolo | character varying(20) |
quantity | integer |
cmf | double precision |
u_qq | double precision |
mont | numeric(10,4) |
vend | boolean |

and a few records like the following (listing purchases and sales for
each item):

=> select * from test_t;

cod_rif | titolo | quantity | cmf | u_qq | mont | vend
---------+--------+----------+---------+--------+---------+------
7 | aaa | 2500 | 25.455 | 0 | 60.0897 | f
34 | bbb | -700 | 0 | 0 | 0.0000 | t
28 | bbb | 2700 | 3.862 | 4.6 | 4.1957 | f
33 | ccc | 10000 | 4.36 | 0 | 4.3600 | f
30 | ccc | 5000 | 1.717 | 1.489 | 1.7170 | f
6 | bbb | -500 | 2.703 | 4.757 | 3.7151 | f
3 | bbb | 500 | 6.057 | 10.129 | 18.7311 | f
32 | ddd | 1500 | 0.55896 | 1.119 | 0.5590 | f
26 | aaa | -1000 | 6.11098 | 6.176 | 6.1110 | t
11 | ddd | -1500 | 10.537 | 4.021 | 20.5815 | t
(10 rows)

The 'summary table' should look more or less like this:

cod_rif | titolo | quantity | cmf | u_qq | mont | vend
---------+--------+----------+---------+--------+---------+------
any | aaa | 1500 | 25.455 | 6.176 | 60.0897 | f
any | bbb | 2000 | 0 | 4.757 | 4.1957 | t
any | ccc | 15000 | 4.36 | 1.489 | 1.7170 | f
any | ddd | 0 | 0.55896 | 1.119 | 20.5815 | f
(4 rows)

With a:

=> select distinct on (sum(quantity)) titolo, sum(quantity) from test_t group by titolo;

I get single rows for each item

titolo | sum
--------+-------
ddd | 0
aaa | 1500
bbb | 2000
ccc | 15000
(4 rows)

but when I include any other field in the query the number of raws
returned grows to include all the original lines (because the other
fields have distinct values).

Do you have any suggestion to achieve the result I want?

Thanks for your attention.
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) ]

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Richard Broersma Jr 2006-11-22 05:20:12 Re: How to get single raws for sums in a summary table?
Previous Message George Weaver 2006-11-21 15:15:34 PostgreSQL doesn't accept connections when Windows Server is rebooted