Re: How to get single raws for sums in a summary table? - Mailing list pgsql-novice

Hello ennio,

i don't really understand your problem.

Why the value for cmf in your summary table is 0 for bbb. Do you want the
"first" value (whatever means first ?) in the field cmf to be shown. It is not
clear at all for me.  Could you please precise it?

Thank you.

Regards,
Peter


---------------------------------------------------------------------------
Peter Paefgen
Landesamt für Datenverarbeitung und Statistik NRW.
Telefon: 0211 9449 2390
Fax: 0211 9449 8390
Mail: peter.paefgen@lds.nrw.de

-----Ursprüngliche Nachricht-----
Von: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] Im Auftrag von Ennio-Sr
Gesendet: Mittwoch, 22. November 2006 01:50
An: pgsql-novice@postgresql.org
Betreff: [NOVICE] How to get single raws for sums in a summary table?

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate