A join of 2 tables with sum(column) > 30
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
Unfortunately I get:
# 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(m.completed)
30 group by u.id, u.first_name order by sum desc limit 3;
ERROR: aggregates not allowed in WHERE clause
LINE 1: ...f_match m where u.id=m.id and u.id like 'DE%' and sum(m.comp...
On Tue, Mar 15, 2011 at 10:43 PM, Vibhor Kumar
<vibhor.kumar@enterprisedb.com> wrote:
Show quoted text
On Mar 16, 2011, at 3:03 AM, Alexander Farber wrote:
# 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..
Try following:
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(m.completed > 30 group by u.id, u.first_name order by sum desc limit 3;
Import Notes
Reply to msg id not found: 23ABA336-9DA8-4DEF-BB4C-544B13DCD261@enterprisedb.com
On Mar 16, 2011, at 3:03 AM, Alexander Farber wrote:
# 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..
Try following:
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(m.completed > 30 group by u.id, u.first_name order by sum desc limit 3;
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com
And same for a simple select-query from1 table (w/o join):
# select id from pref_match where sum(completed) > 30 group by id;
ERROR: aggregates not allowed in WHERE clause
LINE 1: select id from pref_match where sum(completed) > 30 group by...
^
On Mar 16, 2011, at 3:12 AM, Alexander Farber wrote:
Unfortunately I get:
# 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(m.completed)30 group by u.id, u.first_name order by sum desc limit 3;
ERROR: aggregates not allowed in WHERE clause
LINE 1: ...f_match m where u.id=m.id and u.id like 'DE%' and sum(m.comp...
My Bad... Missed you have to use having clause as given below:
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%' group by u.id, u.first_name having sum(m.completed > 30 order by sum desc limit 3;
On Tue, Mar 15, 2011 at 10:43 PM, Vibhor Kumar
<vibhor.kumar@enterprisedb.com> wrote:On Mar 16, 2011, at 3:03 AM, Alexander Farber wrote:
# 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..
Try following:
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(m.completed > 30 group by u.id, u.first_name order by sum desc limit 3;--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com
On Mar 16, 2011, at 3:15 AM, Alexander Farber wrote:
And same for a simple select-query from1 table (w/o join):
# select id from pref_match where sum(completed) > 30 group by id;
ERROR: aggregates not allowed in WHERE clause
LINE 1: select id from pref_match where sum(completed) > 30 group by...
^
Use having clause for aggrgate function,
group by col1, col2 having sum(col) >
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com
On 15 Mar 2011, at 22:33, Alexander Farber wrote:
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;
# 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%'
group by u.id, u.first_name
having sum(m.completed) > 30
order by sum desc limit 3;
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4d80686f235882980188992!
-----Original Message-----
From: Alexander Farber [mailto:alexander.farber@gmail.com]
Sent: Tuesday, March 15, 2011 5:45 PM
To: pgsql-general@postgresql.org
Subject: Re: A join of 2 tables with sum(column) > 30And same for a simple select-query from1 table (w/o join):
# select id from pref_match where sum(completed) > 30 group by id;
ERROR: aggregates not allowed in WHERE clause LINE 1: select
id from pref_match where sum(completed) > 30 group by...
^
You should "HAVING" close (not "WHERE") for an aggregate as a condition:
Select id, sum(col1) from tab
Where id > 10
Group by id
Having sum)col1) >30;
Spend some time reading basic SQL docs/books - it'll help you
tremendously.
Regards,
Igor Neyman
Thank you all for the replies -
On Wed, Mar 16, 2011 at 3:05 PM, Igor Neyman <ineyman@perceptron.com> wrote:
Select id, sum(col1) from tab
Where id > 10
Group by id
Having sum)col1) >30;Spend some time reading basic SQL docs/books - it'll help you
tremendously.
I have already read many SQL-docs (really) and
I've done Perl, PHP, Java, C, ActionScript, etc. programming
at various points of time (for living AND/OR for fun)
and SQL is the most mind-boggling for me.
Regards
Alex