Skip site navigation (1) Skip section navigation (2)

Re: problem with group-by clause/aggreate functions

From: Bastiaan Olij <lists(at)basenlily(dot)nl>
To: Shabala Deshpande <de(dot)shabala(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: problem with group-by clause/aggreate functions
Date: 2008-09-09 22:54:55
Message-ID: 48C6FEBF.8050508@basenlily.nl (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Shabala,

Amazing this works in mysql, I know Sybase does some funky useful things 
on a group by like this but other then that, your query is wrong. Maybe 
mysql has some logic that if a column has a single value within the 
aggregate it doesn't mind the fault.

Any column on which you do not place an aggregate of sorts should appear 
in your group by in order for the group by to work.
For your query the group by should thus include:
cnt, lnk_name, dir, t_rate and rate

Greetz,

Bastiaan Olij

Shabala Deshpande wrote:
> hi,
>  
> I am having a problem with group-by clause/aggreate functions... 
> Following is the query which runs well in mysql....
>  
> query :
>
> select min(rt), cnt, link_name, dir, t_rate, rate from fg_table where 
> obj_type = ? and obj_id = ? and rt > ? and rt_type = 'u' group by 
> link_name, dir;
>
> but when i run it in postgres i get follwing errors:
>  
> DBD::Pg::st execute failed: ERROR:  column "fg_table.cnt" must appear 
> in the GROUP BY clause or be used in an aggregate function 
>  
> same for the t_rate,rate  fields in SELECT clause which are not 
> included in the GROUP BY clause.
>  
>  
> can anyone please guide me?
>  
> thanks n regards,
> ..shabala


-- 
Kindest Regards,

Bastiaan Olij
e-mail/MSN: bastiaan(at)basenlily(dot)nl
web: http://www.basenlily.nl
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij


In response to

pgsql-novice by date

Next:From: Bram Van SteenlandtDate: 2008-09-10 07:33:11
Subject: translation question
Previous:From: Shabala DeshpandeDate: 2008-09-09 09:44:53
Subject: problem with group-by clause/aggreate functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group