Thread: 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) ]
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
am Wed, dem 22.11.2006, um 1:50:05 +0100 mailte Ennio-Sr folgendes: Your desired result my be wrong, for instance for titolo aaa: > > cod_rif | titolo | quantity | cmf | u_qq | mont | vend > ---------+--------+----------+---------+--------+---------+------ > 7 | aaa | 2500 | 25.455 | 0 | 60.0897 | f > 26 | aaa | -1000 | 6.11098 | 6.176 | 6.1110 | t > > 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 quantity: 2500 + -1000 = 1500, okay cmf : 25.455 + 6.11098 != 25.455, wrong u_qq : 0 + 6.176 = 6.176, okay mont : 60.0897 + 6.1110 != 60.0897, wrong > Do you have any suggestion to achieve the result I want? No, because i can't see a regular way or rule to calculate this... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
> 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
;
Oops I tested this and didn't get what you wanted. try this instead.
>
> 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)
^^^^^^
> 'any' as cod_rif,
> titolo,
> sum(quantity),
> cmf,
> u_qq,
> mont,
> vend,
> from
> test_t
> group by
> titolo,
> cmf,
> u_qq,
> mont,
> vend
> ;
Regards,
Richard Broersma Jr.
Hi Richard,
* Richard Broersma Jr <rabroersma@yahoo.com> [221106, 06:53]:
> Oops I tested this and didn't get what you wanted. try this instead.
>
> >
> > 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)
> ^^^^^^
> > 'any' as cod_rif,
> > titolo,
> > sum(quantity),
> > cmf,
> > u_qq,
> > mont,
> > vend,
> > from
> > test_t
> > group by
> > titolo,
> > cmf,
> > u_qq,
> > mont,
> > vend
> > ;
>
> Regards,
> Richard Broersma Jr.
this is exactly what I was looking for :-))) Thank you very much indeed!
I'd been trying various combinations without getting the right one ...
Now a few words for Duncan, Peter and Andreas:
I've a table recording purchases and sales of different items (could be
stocks, shares, car-parts, or whatever you want). Foor each item there
is a negotiation date, an initial price, a quantity, a current price and so on.
What I was looking for was a summary table showing the effective
quantity of the item I'm holding to-date, besides a few other data, and
Richard's solution does give that.
I know that the other data might be inaccurate: to get their
correct value I should devise a different general design for my database
(which I already have in OOo-Calc), may be a table for each item, and a
way to connect the various tables according to the desired target. But
this is only a sort of 'first aid' table ...
I hope to have clarified you doubts.
Thanks again to all of you for your help.
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) ]
* Richard Broersma Jr <rabroersma@yahoo.com> [221106, 06:53]:
> Oops I tested this and didn't get what you wanted. try this instead.
>
> >
> > 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)
> ^^^^^^
> ...
>
> Regards,
> Richard Broersma Jr.
Hi Richard (and others),
I was cheering too early in my previous answer ;)
The query is only apparently giving a sum: in effect the value in each
raw is just one of the values of raws having the same 'titolo'.
What I had in mind was the possibility to achieve the same result I used
to get (quite a few years back) in DBIII, when printing data and setting
subtotals for same items. But this is history ......
Anyway, thanks again for attention and help.
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) ]
>
> Hi Richard (and others),
>
> I was cheering too early in my previous answer ;)
> The query is only apparently giving a sum: in effect the value in each
> raw is just one of the values of raws having the same 'titolo'.
>
> What I had in mind was the possibility to achieve the same result I used
> to get (quite a few years back) in DBIII, when printing data and setting
> subtotals for same items. But this is history ......
>
This should would a little better.
select
distinct on (titolo)
^^^^^^
'any' as cod_rif,
titolo,
(
select A1.sum(quantity) from test_t as A1
where A1.titolo = test_t.titolo
) as sum_qty,
cmf,
u_qq,
mont,
vend,
from
test_t
group by
titolo,
sum_qty,
cmf,
u_qq,
mont,
vend
;
Regards,
Richard Broersma Jr.
* Richard Broersma Jr <rabroersma@yahoo.com> [221106, 12:06]:
> >
> > Hi Richard (and others),
> >
> > I was cheering too early in my previous answer ;)
> > The query is only apparently giving a sum: in effect the value in each
> > raw is just one of the values of raws having the same 'titolo'.
> >
> > What I had in mind was the possibility to achieve the same result I used
> > to get (quite a few years back) in DBIII, when printing data and setting
> > subtotals for same items. But this is history ......
> >
>
> This should would a little better.
>
> select
> distinct on (titolo)
> ^^^^^^
> 'any' as cod_rif,
> titolo,
> (
> select A1.sum(quantity) from test_t as A1
> where A1.titolo = test_t.titolo
> ) as sum_qty,
> cmf,
> u_qq,
> mont,
> vend,
> from
> test_t
> group by
> titolo,
> sum_qty,
> cmf,
> u_qq,
> mont,
> vend
> ;
>
>
> Regards,
> Richard Broersma Jr.
Hi Richard,
this seems to be the right one (with a few minor corrections - see
below).
It works greatly on my test_t (I haven't tested it on the real table yet).
Thanks again for your help.
Regards,
Ennio.
------- this is ok ----------------
select
distinct on (titolo)
'any' as cod_rif,
titolo,
(select sum(quantity) from test_t A1
^
where A1.titolo = test_t.titolo) as "q_res",
cmf, ^^^^^^^
u_qq,
mont,
vend
^
from
test_t
group by
titolo,
quantity,
-- sum_qty,
cmf,
u_qq,
mont,
vend
;
-----------------------------------
PS: I didn't go through all pg-documentation so my guess might be a bit
of hazard: shouldn't the 'distinct on (some_field)' do all the job on its own
with no need for the second (sub)select? Otherwise there seems to be no
difference between 'select distinct on (fx) f1, f2, .,fx,. ,fn' and
'select distinct f1, f2, .,fx,. fn'.
--
[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) ]
> ----------------------------------- > > PS: I didn't go through all pg-documentation so my guess might be a bit > of hazard: shouldn't the 'distinct on (some_field)' do all the job on its own > with no need for the second (sub)select? Otherwise there seems to be no > difference between 'select distinct on (fx) f1, f2, .,fx,. ,fn' and > 'select distinct f1, f2, .,fx,. fn'. > I think that distinct on works like a where clause. So if filter out records rather than grouping them. So you end up with a sum of 1 record. Regards, Richard Broersma jr.
* Richard Broersma Jr <rabroersma@yahoo.com> [221106, 15:30]:
>
> > -----------------------------------
> >
> > PS: I didn't go through all pg-documentation so my guess might be a bit
> > of hazard: shouldn't the 'distinct on (some_field)' do all the job on its own
> > with no need for the second (sub)select? Otherwise there seems to be no
> > difference between 'select distinct on (fx) f1, f2, .,fx,. ,fn' and
> > 'select distinct f1, f2, .,fx,. fn'.
> >
>
> I think that distinct on works like a where clause. So if filter out records rather than grouping
> them. So you end up with a sum of 1 record.
>
> Regards,
>
> Richard Broersma jr.
Thank you, Richard, for this further element of knowledge!
In the meantime I've tested the real table and your query works like a
charm: now, finally, my nice (coloured) summary table tells the 'truth' :-)))
Cheers,
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) ]