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

> 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?

This is untested. Also I am not sure that this is what you want, but I do not know how you arrive
at the values in your summary table.

select
        distinct on (titolo,cmf,u_qq,mont,vend)
        'any' as cod_rif,
        titolo,
        sum(quantity),
        cmf,
        u_qq,
        mont,
        vend,
from
        test_t
group by
        titolo,
        cmf,
        u_qq,
        mont,
        vend
;