Sorting months while im converting them to text

Question

I have to do a consult which must give me the following information:

Month    | Quantity
-------------------
January  | XX
February | XX
...      | ..

So, I thought in:

select to_char(to_timestamp(to_char(date_part('month', orderdate), '999'), 'MM'), 'Mon'), count(*)  as quantity from orders group by 1 ORDER BY 1

The problem is: months were sorted by "text" I mean:

Apr
Aug
Dec
...

How to solve it?

Answer 1

I suggest date_trunc() instead. It truncates date / timestamp to the given unit.

For two reasons:

1. You want the number of orders in August of a particular year, like 2012, not the sum for August of all years in the table. date_trunc('month', orderdate) does exactly that and prevents that you mix multiple years by accident. You get multiple rows for multiple years.

2. You can both ORDER BY and GROUP BY this one expression, the query is a bit faster.

SELECT to_char(date_trunc('month', orderdate), 'Mon') AS "Month" -- repeat expr.
      ,count(*) AS "Quantity"
FROM   orders
GROUP  BY date_trunc('month', orderdate)   -- 1 item covers it
ORDER  BY date_trunc('month', orderdate);

db<>fiddle here
_{Old sqlfiddle}

For full month names, like your first example implies:

to_char(date_col, 'Month')

For non-English, localized names:

to_char(date_col, 'TMMonth')

Details in the manual.

Answer 2

First of all, your to_char is a lot more complicated that it needs to be, just this:

to_char(orderdate, 'Mon')

should be sufficient.

You're grouping and ordering by the first value that you select, that's what your 1 means. So of course the results are being sorted by month name, that's what you're asking for. Instead you want to group and order by the month component of the date, not its string representation. Something like this:

select to_char(orderdate, 'Mon') as "Month",
       count(*) as "Quantity"
from orders
group by extract(month from orderdate), to_char(orderdate, 'Mon')
order by extract(month from orderdate)

You need both values in the GROUP BY to make it play nice with both your SELECT and your ORDER BY at the same time.