2

I have a database formed like this:

 descr    | hours |   timer    
----------+-------+------------
 foo      |   1.3 | 2012-07-14
 foo      |   2.5 | 2012-07-15
 foo      |  2.35 | 2012-07-16
 bar      |     1 | 2012-08-16
 baz      |     1 | 2012-08-16
 buh      |     5 | 2012-08-17

This does what I expect:

abc=# select extract('month' from timer) as Month, sum(hours) from foo group by Month     order by 1;

 month | sum  
 ------+------
 7     | 6.15
 8     |    7

This doesn't quite do what I expect:

abc=# select to_char(timer, 'Month'), sum(hours) from foo group by 1 order by 1 asc;

 to_char  | sum  
----------+------
August    |    7
July      | 6.15

The desired result is:

month     | sum
----------+-----
July      | 6.15
August    |    7

I understand why things are happening in #3, but how do I achieve the desired result, within PostgreSQL, in the most efficient manner possible?

Arima
  • 195
  • 6
  • 1
    Why not to use `extract('month' from timer)` in `order by` keyword? – Aleksandr Dezhin Oct 10 '12 at 15:51
  • What I want is the month names (sorted by the actual month, not alphabetically). Extract only seems to give me the numeric portion of the month. See the desired result (last code paste). – Arima Oct 10 '12 at 15:58

1 Answers1

4

You can use extract expression in ORDER BY section:

SELECT to_char(timer, 'Month'), SUM(hours)
FROM foo
GROUP BY to_char(timer, 'Month'), extract('Month' from timer)
ORDER BY extract('Month' from timer) ASC;

This query does desired result.

Aleksandr Dezhin
  • 636
  • 4
  • 7