Re: Problem with select statement

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(at)postgresql(dot)org