CODEWITHSUNDEEP
sqloracle-database
Rodi Marcus
Rodi Marcus

Reputation: 107

order by month oracle

I'm trying to make a simple query to find the average car sales per month from a table called "salestransaction".

My code:

    select to_char(st.dateofsale, 'MON') as mnth, count(*) / 2 as Average_Car_Sales
    from salestransaction st
    group by to_char(st.dateofsale, 'MON')
    order by to_char(st.dateofsale, 'MON');

My order by month is not outputting the correct order. What can I do to make it output by month? starting from JAN - DEC?

Thank you.

Upvotes: 2

Views: 16736

Answers (3)

dani herrera
dani herrera

Reputation: 51715

An easy approach is to add month number to group by, then order by this field:

select to_char(st.dateofsale, 'MON') as mnth, 
       count(*) / 2 as Average_Car_Sales
from salestransaction st
group by EXTRACT(month FROM st.dateofsale),
         to_char(st.dateofsale, 'MON')
order by EXTRACT(month FROM st.dateofsale);

If you try to order without aggregate function or without adding month number in group by expression then you will get ORA-00979: not a GROUP BY expression error

To avoid Extract you can use to_char with MM pattern:

select to_char(st.dateofsale, 'MON') as mnth, 
       count(*) / 2 as Average_Car_Sales
from salestransaction st
group by to_char(st.dateofsale, 'MM'),
         to_char(st.dateofsale, 'MON')
order by to_char(st.dateofsale, 'MM');

Upvotes: 6

N.Be
N.Be

Reputation: 38

It is because you order them according to the month name in this case, december comes before january. You need to get their numeric values. Here is an example from oracle documantation:

SELECT EXTRACT(month FROM order_date) "Month",

COUNT(order_date) "No. of Orders"

FROM orders

GROUP BY EXTRACT(month FROM order_date)

ORDER BY EXTRACT(month FROM order_date)

You can check it from this link for more detailed information:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270783

My recommendation:

order by min(st.dateofsale);

That is, just pull a value out for each group and use that for the ordering.

If you have data from multiple years, the above might not work. Instead:

order by min(extract(month from st.dateofsale))

Upvotes: 5

Related Questions