Re: Problem with select statement

[phil campaigne <pcampaigne@xxxxxxxxxxx>]

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@xxxxxxxxxxxxxx






---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq