How to get single raws for sums in a summary table?
От | Ennio-Sr |
---|---|
Тема | How to get single raws for sums in a summary table? |
Дата | |
Msg-id | 20061122005005.GA23196@deby.ei.hnet обсуждение исходный текст |
Ответы |
Re: How to get single raws for sums in a summary table?
Re: How to get single raws for sums in a summary table? Re: How to get single raws for sums in a summary table? |
Список | 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) ]
В списке pgsql-novice по дате отправления: