Postgres Pro
Downloads
Home   >   mailing lists

Thread: Formatting Functions and Group By

Formatting Functions and Group By

From
Terry Brick
Date:
Hi,
I'm porting a bunch of queries from MySQL to Postgres 7.4 and am having a problem with one
particular area.  For example, a query like this works in MySQL:

select to_char(myCol,'Mon YY')
from myTable
group by to_char(myCol,'MM YYYY')
order by to_char(myCol,'MM YYYY')

Postgres will give me an error saying that "to_char(myCol,'Mon YY')" must be in the 'group by'.  I
understand why that normally needs to be the case, but in theory, it would be ok here because they
are actually the same values (in the select and group by) but just formatted differently.  I just
want the query to be grouped and ordered by month and year, but to be formatted differently in the
output.  

Any ideas?

Thanks!!

    
__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway 
http://promotions.yahoo.com/design_giveaway/

Re: Formatting Functions and Group By

From
Bruno Wolff III
Date:
On Tue, Apr 13, 2004 at 09:13:05 -0700, Terry Brick <terry_brick2000@yahoo.com> wrote:
> Hi,
> I'm porting a bunch of queries from MySQL to Postgres 7.4 and am having a problem with one
> particular area.  For example, a query like this works in MySQL:
> 
> select
>   to_char(myCol,'Mon YY')
> from
>   myTable
> group by
>   to_char(myCol,'MM YYYY')
> order by
>   to_char(myCol,'MM YYYY')
> 
> Postgres will give me an error saying that "to_char(myCol,'Mon YY')" must be in the 'group by'.  I
> understand why that normally needs to be the case, but in theory, it would be ok here because they
> are actually the same values (in the select and group by) but just formatted differently.  I just
> want the query to be grouped and ordered by month and year, but to be formatted differently in the
> output.  
> 
> Any ideas?

You could do something like the following. It works in 7.4, but I am not
sure if it is completely standard.

area=> select to_char(date_trunc('month',current_date), 'MM YYYY')
area->   group by date_trunc('month',current_date);to_char
---------04 2004
(1 row)

Re: Formatting Functions and Group By

From
Tom Lane
Date:
Terry Brick <terry_brick2000@yahoo.com> writes:
> I'm porting a bunch of queries from MySQL to Postgres 7.4 and am having a problem with one
> particular area.  For example, a query like this works in MySQL:

> select
>   to_char(myCol,'Mon YY')
> from
>   myTable
> group by
>   to_char(myCol,'MM YYYY')
> order by
>   to_char(myCol,'MM YYYY')

Ah, good ol' MySQL :-( ... let the user do what he wants whether the
result is well defined or not ...

I'd suggest doing the grouping/ordering numerically rather than
textually.  For instance,

select to_char(date_trunc('month', myCol), 'Mon YY')
from myTable
group by date_trunc('month', myCol)
order by date_trunc('month', myCol);

Now this assumes you really want a time-based ordering, which the quoted
example doesn't give --- you've got month sorting to the left of year,
is that really what you want?  If it is then you'd need to go

group by date_trunc('month', myCol)
order by to_char(date_trunc('month', myCol), 'MM YYYY')
        regards, tom lane

Re: Formatting Functions and Group By

From
Terry Brick
Date:
Thank you both for your responses.  That's just what I needed.... and thanks for catching my
mistake Tom.  And may I say that I am VERY happy to be moving to Postgres.  The lack of a native
Win32 version was thing only thing holding us back from Postgres previously.  
I think this is the only kind of query I will have had difficulty porting.  I'm looking forward to
ditching MySQL and never looking back!! :) 

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 
> Ah, good ol' MySQL :-( ... let the user do what he wants whether the
> result is well defined or not ...
> 
> I'd suggest doing the grouping/ordering numerically rather than
> textually.  For instance,
> 
> select
>   to_char(date_trunc('month', myCol), 'Mon YY')
> from
>   myTable
> group by
>   date_trunc('month', myCol)
> order by
>   date_trunc('month', myCol);
> 
> Now this assumes you really want a time-based ordering, which the quoted
> example doesn't give --- you've got month sorting to the left of year,
> is that really what you want?  If it is then you'd need to go
> 
> group by
>   date_trunc('month', myCol)
> order by
>   to_char(date_trunc('month', myCol), 'MM YYYY')
> 
>             regards, tom lane


    
__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway 
http://promotions.yahoo.com/design_giveaway/