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?  ("Paefgen, Peter (LDS)" <Peter.Paefgen@lds.nrw.de>)
Re: How to get single raws for sums in a summary table?  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Re: How to get single raws for sums in a summary table?  (Richard Broersma Jr <rabroersma@yahoo.com>)
Список 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 по дате отправления:

Предыдущее
От: George Weaver
Дата:
Сообщение: PostgreSQL doesn't accept connections when Windows Server is rebooted
Следующее
От: "Paefgen, Peter (LDS)"
Дата:
Сообщение: Re: How to get single raws for sums in a summary table?