From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | A join of 2 tables with sum(column) > 30 |
Date: | 2011-03-15 21:33:12 |
Message-ID: | AANLkTi=Y2k5hkj82SM8ponfqq9jD20CpB_w1MMNR=FWQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2011-03-15 21:33:43 | Re: Enable/Disable Triggers |
Previous Message | John R Pierce | 2011-03-15 21:04:04 | Re: How to add hosts to pg_hba.conf and postgresql.conf? |