Re: Problem with select statement

phil campaigne <pcampaigne@xxxxxxxxxxx> · Mon, 21 Feb 2005 12:52:40 -0500

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings