Re: Problem with select statement
От | phil campaigne |
---|---|
Тема | Re: Problem with select statement |
Дата | |
Msg-id | 42192524.5000002@charter.net обсуждение исходный текст |
Ответ на | Problem with select statement (phil campaigne <pcampaigne@charter.net>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: