Re: How to get single raws for sums in a summary table?

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