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

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

Responses

Browse pgsql-general by date

  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?