Thread: Problem with select statement
Yes Gnari it works now! all I had to do in addition to your advice was alias the sub select: hardwoodthunder=# select player_number,player_name,cum_score,sum(a),sum(c),sum(t) from ( select player_number, player_name, cum_score, (select player_points where aspect='A') as A ,(select player_points where aspect='C') as C, (select player_points where aspect='T') as T from (select * from player as a,teamshare as b where a.player_number=b.player) as c) as B group by player_number, player_name, cum_score; player_number | player_name | cum_score | sum | sum | sum ---------------+-------------+-----------+------+------+----- 40 | R. Perkins | 4 | 0.27 | 0.33 | 42 | S. Randolph | 2 | 0.54 | 0.66 | 0.8 42 | S. Randolph | 4 | 0.27 | 0.33 | 0.8 (3 rows) Now what about the total? How do I add another column that is the sum of a,c,t? I tried sum(sum(a),sum(c),sum(t)) but it doesn't work. Thanks, Phil
On Sun, 2005-02-20 at 18:18 -0500, phil campaigne wrote: > Yes Gnari it works now! > all I had to do in addition to your advice was alias the sub select: > > hardwoodthunder=# select > player_number,player_name,cum_score,sum(a),sum(c),sum(t) from ( select > player_number, player_name, cum_score, (select player_points where > aspect='A') as A ,(select player_points where aspect='C') as C, (select > player_points where aspect='T') as T from (select > * from player as a,teamshare as b where a.player_number=b.player) as c) > as B group by player_number, player_name, cum_score; > player_number | player_name | cum_score | sum | sum | sum > ---------------+-------------+-----------+------+------+----- > 40 | R. Perkins | 4 | 0.27 | 0.33 | > 42 | S. Randolph | 2 | 0.54 | 0.66 | 0.8 > 42 | S. Randolph | 4 | 0.27 | 0.33 | 0.8 > (3 rows) > > Now what about the total? How do I add another column that is the sum > of a,c,t? > maybe something like: select player_number,player_name,cum_score, suma,sumc,sumt, coalesce(suma,0)+coalesce(sumc,0)+coalesce(sumt,0) as sumtotal from ( select player_number,player_name,cum_score,sum(a) as a,sum(c) as c,sum(t) as t from ( select player_number, player_name, cum_score, (select player_points where aspect='A') as A ,(select player_points where aspect='C') as C, (select player_points where aspect='T') as T from (select * from player as a,teamshare as b where a.player_number=b.player) as c) as B group by player_number, player_name, cum_score) as foo); gnari
Ragnar Hafstað wrote: >On Sun, 2005-02-20 at 18:18 -0500, phil campaigne wrote: > > >>Yes Gnari it works now! >>all I had to do in addition to your advice was alias the sub select: >> >>hardwoodthunder=# select >>player_number,player_name,cum_score,sum(a),sum(c),sum(t) from ( select >>player_number, player_name, cum_score, (select player_points where >>aspect='A') as A ,(select player_points where aspect='C') as C, (select >>player_points where aspect='T') as T from (select >>* from player as a,teamshare as b where a.player_number=b.player) as c) >>as B group by player_number, player_name, cum_score; >> player_number | player_name | cum_score | sum | sum | sum >>---------------+-------------+-----------+------+------+----- >> 40 | R. Perkins | 4 | 0.27 | 0.33 | >> 42 | S. Randolph | 2 | 0.54 | 0.66 | 0.8 >> 42 | S. Randolph | 4 | 0.27 | 0.33 | 0.8 >>(3 rows) >> >>Now what about the total? How do I add another column that is the sum >>of a,c,t? >> >> >> > >maybe something like: >select player_number,player_name,cum_score, suma,sumc,sumt, >coalesce(suma,0)+coalesce(sumc,0)+coalesce(sumt,0) as sumtotal >from ( select >player_number,player_name,cum_score,sum(a) as a,sum(c) as c,sum(t) as t from ( select >player_number, player_name, cum_score, (select player_points where >aspect='A') as A ,(select player_points where aspect='C') as C, (select >player_points where aspect='T') as T from (select >* from player as a,teamshare as b where a.player_number=b.player) as c) >as B group by player_number, player_name, cum_score) as foo); > >gnari > > > > > gnari, Yup it works! all I changed in your code was parens around the a,c,t. Here is the final working code: select player_number,player_name,cum_score,sum(a) as Asum,sum(c) as Csum,sum(t) as Tsum, coalesce(sum(a),0)+coalesce(sum(c),0)+coalesce(sum(t),0) as sumtotal from ( select player_number, player_name, cum_score, (select player_points where aspect='A') as A ,(select player_points where aspect='C') as C, (select player_points where aspect='T') as T from (select * from player as a,teamshare as b where a.player_number=b.player) as c) as B group by player_number, player_name, cum_score; player_number | player_name | cum_score | asum | csum | tsum | sumtotal ---------------+-------------+-----------+------+------+------+---------- 40 | R. Perkins | 4 | 0.27 | 0.33 | | 0.6 42 | S. Randolph | 2 | 0.54 | 0.66 | 0.8 | 2 42 | S. Randolph | 4 | 0.27 | 0.33 | 0.8 | 1.4 (3 rows) or, taking out the cum_score qualifier: select player_number,player_name,sum(a) as Asum,sum(c) as Csum,sum(t) as Tsum, coalesce(sum(a),0)+coalesce(sum(c),0)+coalesce(sum(t),0) as sumtotal from ( select player_number, player_name, (select player_points where aspect='A') as A ,(select player_points where aspect='C') as C, (select player_points where aspect='T') as T from (select * from player as a,teamshare as b where a.player_number=b.player) as c) as B group by player_number, player_name; player_number | player_name | asum | csum | tsum | sumtotal ---------------+-------------+------+------+------+---------- 40 | R. Perkins | 0.27 | 0.33 | | 0.6 42 | S. Randolph | 0.81 | 0.99 | 1.6 | 3.4 (2 rows) Thanks so much, Phil pgsql-general@postgresql.org
Ragnar Hafstað wrote:
>On Sun, 2005-02-20 at 20:01 -0500, phil campaigne wrote:
>
>
>>Hi gnari,
>>I forgot one thing. I also need to restrict the selecct with two more
>>qualifiers
>>where contest_id =1 and team='Duke'
>>I'm confused where to put it in the select statement. I tried this but
>>it doesn't work:
>>
>>
>
>you dont say where these fit in. they can obviously not be in B,
>as that one did not include such columns.
>if they are columns of player or teamshare, then you could probably:
>
>select player_number,
> player_name,
> sum(a) as Asum,
> sum(c) as Csum,
> sum(t) as Tsum,
> coalesce(sum(a),0)+coalesce(sum(c),0)+coalesce(sum(t),0) as sumtotal
>from (
> select player_number,
> player_name,
> (select player_points where aspect='A') as A ,
> (select player_points where aspect='C') as C,
> (select player_points where aspect='T') as T
> from (
> select *
> from player as a,teamshare as b
> where a.player_number=b.player
> and contest_id=1 and team='Duke'
> ) as c
> ) as B
>group by player_number, player_name;
>
>
>gnari
>
>
>
>
>
Gnari,
Thanks, and hello to Iceland from Boston. The way you formated the text
is a lesson for me. It makes the code much more intuitive.... but
adding the phrase:
and contest_id=2 and teamshare.team='Duke'
as below produces results 8 times as large as the correct results.
select player_number,player_name,sum(a) as Asum,sum(c) as Csum,sum(t) as
Tsum,
coalesce(sum(a),0)+coalesce(sum(c),0)+coalesce(sum(t),0) as sumtotal
from ( select player_number, player_name, (select player_points where
aspect='A') as A ,(select player_points where aspect='C') as C, (select
player_points where aspect='T') as T from (select * from player as
a,teamshare
as b where a.player_number=b.player and teamshare.contest_id=2 and
teamshare.team='Duke' ) as c) as B group
by player_number, player_name;
NOTICE: Adding missing FROM-clause entry in subquery for table "teamshare"
player_number | player_name | asum | csum | tsum | sumtotal
---------------+-------------+------+------+------+----------
40 | R. Perkins | 2.16 | 2.64 | | 4.8
42 | S. Randolph | 6.48 | 7.92 | 12.8 | 27.2
(2 rows)
I did get this NOTICE that I don't understand:
NOTICE: Adding missing FROM-clause entry in subquery for table "teamshare"
I tried changing the select statement to be
select * from player as a *** and from *** teamshare as b
But that didn't work. Here are my table definitions:
Table "public.teamshare"
Column | Type | Modifiers
---------------+-----------------------+-----------
teamshare_id | integer | not null
possession_id | integer | not null
contest_id | integer | not null
team | character varying(8) | not null
cum_score | integer | not null
player | integer | not null
aspect | character(1) | not null
player_points | double precision |
organization | character varying(14) |
Indexes: teamshare_pkey primary key btree (teamshare_id)
Table "public.player"
Column | Type | Modifiers
---------------------+-----------------------+-----------
player_id | integer | not null
player_number | character varying(3) | not null
player_name | character varying(14) | not null
team | character varying(24) | not null
organization | character varying(12) | not null
player_compensation | integer |
Indexes: player_pkey primary key btree (player_id)
I can't think of any rationale for placing the qualifying phrase
anywhere else. Any Ideas?
thanks,
Phil