Обсуждение: Problem with select statement

Поиск
Список
Период
Сортировка

Problem with select statement

От
phil campaigne
Дата:
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





Re: Problem with select statement

От
Ragnar Hafstað
Дата:
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



Re: Problem with select statement

От
phil campaigne
Дата:
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






Re: Problem with select statement

От
phil campaigne
Дата:
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