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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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