CODEWITHSUNDEEP
sqlpostgresql
Vik G
Vik G

Reputation: 639

postgres order chronologically by month

I have a master table which has the following info for some items

a timestamp called created_date - sample value -

2020-10-08 22:38

from this I extract the month and month name

EXTRACT(month from created_date) as month,
date_format(cast (created_date as timestamp), '%M') month_name,

I then do some other logic to find average values in a column

so my final query looks like this with query as ( SELECT EXTRACT(month from created_date) as month, date_format(cast (created_date as timestamp), '%M') month_name, unit_price FROM parent_table WHERE -- some criteria here )

select month_name, month, avg(unit_price) as avg_unit_price from query 
group by month_name, month

The final output looks like the below table.

April 4 7.35    
December 12 43.79   
March 3 17.41   
October 10  50.15   
November 11 45.82   
September 9 58.64   
January 1   36.55   
February 2  25.07

Now I want to order this by date in chronological order, if I order by month then the order is not correct. I have tried to order with diff columns both month and month_name but it isn't correct.

Any ideas on what I can do to fix this so the ordered data looks like this

September 9 58.64   
October 10  50.15   
November 11 45.82   
January 1   36.55   
February 2  25.07
March 3 17.41   
December 12 43.79
April 4 7.35

Upvotes: 0

Views: 672

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Dispense with the subquery . . . or include created_date in it.

Then you can use:

order by min(created_date)

Or, if it is an aggregation query, include min(created_date) in the subquery and use:

order by min(min_created_date)

However, I would advise that you include the year in the aggregation as well:

select year(created_date), 
       EXTRACT(month from created_date) as month,
       date_format(cast (created_date as timestamp), '%M') as month_name
       avg(unit_price) as avg_unit_price 
from query 
group by month_name, month, year
order by min(created_date);

Or, my preference is to just truncate the date:

select date_trunc('month', created_date) as yyyymm,
       avg(unit_price) as avg_unit_price
from . . .
group by yyyymm
order by min(created_date);

Upvotes: 1

Related Questions