Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group