Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select/Group by/Order by question

Select/Group by/Order by question

From
Mike Nolan
Date:
I'm trying to create a summary log by hour.  Here's the query (somewhat
simplified):

   select  to_char(mtrantime,'mm-dd hh AM') as datetime,
   count(*) as tot from memtran
   group by datetime
   order by datetime;

The problem is this produces the data in the following order:

 datetime   | tot
-------------+-----
04-08 01 PM |  14
04-08 02 PM |  15
04-08 03 PM |  23
04-08 07 AM |   8
04-08 08 AM |  54
04-08 09 AM |  30
04-08 10 AM |  11
04-08 11 AM |  10
04-08 11 PM |   7
04-08 12 PM |  10

What I'd really like is to get it in chronological order by hour:

04-08 07 AM |   8
04-08 08 AM |  54
04-08 09 AM |  30
04-08 10 AM |  11
04-08 11 AM |  10
04-08 12 PM |  10
04-08 01 PM |  14
04-08 02 PM |  15
04-08 03 PM |  23
04-08 11 PM |   7

I would prefer not to show the time of day in 24 hour format, but
there doesn't appear to be a way to order by something that
isn't in the select and group by clause and I don't want to display
the hour twice.

Putting the AM/PM before the HH (which looks a bit clumsy) almost works,
except that 12PM gets sorted to the bottom after 11PM.

Is there an easy way around this?
--
Mike Nolan

Re: Select/Group by/Order by question

From
Tom Lane
Date:
Mike Nolan <nolan@gw.tssi.com> writes:
>    select  to_char(mtrantime,'mm-dd hh AM') as datetime,
>    count(*) as tot from memtran
>    group by datetime
>    order by datetime;
> The problem is this produces the data in the following order:
> ...
> What I'd really like is to get it in chronological order by hour:

You are grouping/ordering by the textual result of to_char(),
in which PM naturally follows AM.  I think the behavior you
want would come from grouping/ordering by the underlying
timestamp column "mtrantime".

            regards, tom lane

Re: Select/Group by/Order by question

From
Mike Nolan
Date:
> You are grouping/ordering by the textual result of to_char(),
> in which PM naturally follows AM.  I think the behavior you
> want would come from grouping/ordering by the underlying
> timestamp column "mtrantime".

Well, I need it grouped by hour, but that led me to the solution:

select to_char(date_trunc('hour',mtrantime),'mm-dd hh AM') as
datetime, count(*) as tot, from memtran
group by mtranoper, date_trunc('hour',mtrantime)
order by mtranoper, date_trunc('hour',mtrantime)

I knew there had to be a straight-forward solution.  Thanks Tom.
--
Mike Nolan

Re: Select/Group by/Order by question

From
"Michael Fork"
Date:
How about:

    select  to_char(mtrantime,'mm-dd hh AM') as datetime,
    to_char(mtrantime,'AM') as sort_field,
    count(*) as tot from memtran
    group by sort_field, datetime
    order by sort_field, datetime;

Then ignore the sort_field column?

Michael

"Mike Nolan" <nolan@gw.tssi.com> wrote in message
news:200404082349.i38NnN45017008@gw.tssi.com...
> I'm trying to create a summary log by hour.  Here's the query (somewhat
> simplified):
>
>    select  to_char(mtrantime,'mm-dd hh AM') as datetime,
>    count(*) as tot from memtran
>    group by datetime
>    order by datetime;
>
> The problem is this produces the data in the following order:
>
>  datetime   | tot
> -------------+-----
> 04-08 01 PM |  14
> 04-08 02 PM |  15
> 04-08 03 PM |  23
> 04-08 07 AM |   8
> 04-08 08 AM |  54
> 04-08 09 AM |  30
> 04-08 10 AM |  11
> 04-08 11 AM |  10
> 04-08 11 PM |   7
> 04-08 12 PM |  10
>
> What I'd really like is to get it in chronological order by hour:
>
> 04-08 07 AM |   8
> 04-08 08 AM |  54
> 04-08 09 AM |  30
> 04-08 10 AM |  11
> 04-08 11 AM |  10
> 04-08 12 PM |  10
> 04-08 01 PM |  14
> 04-08 02 PM |  15
> 04-08 03 PM |  23
> 04-08 11 PM |   7
>
> I would prefer not to show the time of day in 24 hour format, but
> there doesn't appear to be a way to order by something that
> isn't in the select and group by clause and I don't want to display
> the hour twice.
>
> Putting the AM/PM before the HH (which looks a bit clumsy) almost works,
> except that 12PM gets sorted to the bottom after 11PM.
>
> Is there an easy way around this?
> --
> Mike Nolan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: Select/Group by/Order by question

From
Mike Nolan
Date:
> How about:
>
>     select  to_char(mtrantime,'mm-dd hh AM') as datetime,
>     to_char(mtrantime,'AM') as sort_field,
>     count(*) as tot from memtran
>     group by sort_field, datetime
>     order by sort_field, datetime;
>
> Then ignore the sort_field column?

I usually don't like to send managers reports with data labeled
'ignore this column'.  :-)

With Tom's help, I found a solution.
--
Mike Nolan