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
