CODEWITHSUNDEEP
sqlpostgresql
Will
Will

Reputation: 4469

Can't use to_date() on aliased column

I'm trying to sort by group and sort by month. Since I am using to_char(date_field, 'Month') as month to get the month as a string instead of a date. This causes the results to have the months sorted alphabetically rather than chronologically.

select fac.facility_cd,
  fac.name,
  to_char(oh.create_date, 'Month') as month,
  count(ol.ord_line_id) as number_orders,
  sum(ol.retail_price) as total
from ord_header oh
  join ord_line ol using(ord_id)
  join ord_subline os using(ord_line_id)
  join facility fac using(facility_cd)
group by 1, 2, 3
order by 1, 2, 3;

Which produces:

fac_cd |                   name                 |   month   | number_orders |      total
-------+------------------------------------------+-----------+---------------+------------------
 502 | ART FURNITURE CO                         | December  |             1 |             1099
 503 | ABCOASTER                                | December  |             5 |           144.75
 503 | ABCOASTER                                | January   |             4 |            115.8
 503 | ABCOASTER                                | November  |             2 |             57.9
 205 | CHAR-GRILLER (A&J MFG LLC)               | April     |             6 |              424
 205 | CHAR-GRILLER (A&J MFG LLC)               | August    |            29 |             4786
 205 | CHAR-GRILLER (A&J MFG LLC)               | December  |            21 |          2397.98
 205 | CHAR-GRILLER (A&J MFG LLC)               | February  |             5 |              525
 205 | CHAR-GRILLER (A&J MFG LLC)               | January   |             2 |              148
 205 | CHAR-GRILLER (A&J MFG LLC)               | July      |            16 |             1504
 205 | CHAR-GRILLER (A&J MFG LLC)               | June      |            18 |             1762
 205 | CHAR-GRILLER (A&J MFG LLC)               | March     |            10 |              720

Note the months are alphabetical. I searched SO for an answer, but when I try to fix this, I tried:

order by 1, 2, to_date(3, 'Month');

Which gives the error:

ERROR:  function to_date(integer, unknown) does not exist
LINE 11: order by 1, 2, to_date(3, 'Month');
                        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I also tried this:

order by 1, 2, extract('Month' from oh.create_date);

Which gives returns this error:

ERROR:  column "oh.create_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 11: order by 1, 2, extract('Month' from oh.create_date);
                                             ^

How can I change this query to order months by the order they occur?

Upvotes: 0

Views: 665

Answers (1)

Yordan Georgiev
Yordan Georgiev

Reputation: 36

I suppose that this is postgresql since you try to use to_date()? Anyway, you could try what @vkp suggests but also change the column in the group by clause:

group by 1, 2, date_trunc('month',oh.create_date)
order by 1, 2, date_trunc('month',oh.create_date);

I can't test it right now so if it doesn't work please tell me to look for decision later today :)

EDIT

Not the cleanest way but something like that should do the trick:

SELECT
  t.facility_cd,
  t.name,
  to_char(to_timestamp(m::text, 'MM'), 'Month') as month,
  t.number_orders,
  t.total
FROM 
(select fac.facility_cd,
  fac.name,
  extract(month from oh.create_date) as m,
  count(ol.ord_line_id) as number_orders,
  sum(ol.retail_price) as total
from ord_header oh
  join ord_line ol using(ord_id)
  join ord_subline os using(ord_line_id)
  join facility fac using(facility_cd)
group by 1, 2, 3
order by 1, 2, 3) as t

Upvotes: 2

Related Questions