Обсуждение: problem with group-by clause/aggreate functions

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;

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@basenlily.nl
web: http://www.basenlily.nl
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij