Обсуждение: 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

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

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:
>
> 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; 
>

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
byu.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) > 30
>
> 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...
>                                         ^
>

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