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