Problem with select statement

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