Search Postgresql Archives

A join of 2 tables with sum(column) > 30

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux