A join of 2 tables with sum(column) > 30
От | Alexander Farber |
---|---|
Тема | A join of 2 tables with sum(column) > 30 |
Дата | |
Msg-id | AANLkTi=Y2k5hkj82SM8ponfqq9jD20CpB_w1MMNR=FWQ@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: A join of 2 tables with sum(column) > 30
Re: A join of 2 tables with sum(column) > 30 |
Список | pgsql-general |
Hello, I have a table holding number of games per week for each user: # select id,completed,yw from pref_match limit 3; id | completed | yw ----------------+-----------+--------- OK2650139676 | 10 | 2011-03 OK513367704098 | 20 | 2011-03 OK513367704098 | 30 | 2011-04 (3 rows) and then another table with user names: # select id, first_name from pref_users limit 3; id | first_name ----------------+------------ OK272457241702 | Alex OK123280785043 | Felix OK513367704098 | Alissa (3 rows) I'm trying to print the first_name's of players, who played more than 30 complete games (in total): # select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and m.completed > 30 group by u.id, u.first_name order by sum desc limit 3; id | first_name | sum --------+------------+----- DE9143 | BATISTA | 619 DE8890 | CBETA | 485 DE9163 | andrej75 | 458 (3 rows) This seems to work, but the condition above is .... m.completed > 30 i.e. it wants 30 games or more completed per week? I'm trying to change it to a sum, but get the error: # select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and sum > 30 group by u.id, u.first_name order by sum desc limit 3; ERROR: column "sum" does not exist LINE 4: ...f_match m where u.id=m.id and u.id like 'DE%' and sum > 30 g... Any suggestions please? I've tried "... sum(m.completed) as total" too... Regards Alex
В списке pgsql-general по дате отправления: