Home » SQL & PL/SQL » SQL & PL/SQL » Order by Month (Oracle 10g)
Order by Month [message #576051] Thu, 31 January 2013 02:35 Go to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi All,

How I can see the order by employees,year wise and monthwise (month should start from january and so on..)
SELECT ENAME,TO_CHAR(HIREDATE,'yyyy') YEAR,TO_CHAR(HIREDATE,'Month') MONTH ,count(*) total
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'yyyy') ,TO_CHAR(HIREDATE,'Month');


Regards,
Nathan
Re: Order by Month [message #576052 is a reply to message #576051] Thu, 31 January 2013 02:39 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd use ORDER BY clause. Years are simple - they are numbers already. For months, don't use 'Month' format mask but 'MM'.
Re: Order by Month [message #576053 is a reply to message #576051] Thu, 31 January 2013 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, note that your statement is not correct: ENAME is not in the GROUP BY clause and is not an aggregate expression.

Regards
Michel
Re: Order by Month [message #576054 is a reply to message #576051] Thu, 31 January 2013 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you still want month in name an use SQL*Plus, you can add a "hidden" column (removing ENAME to make the query valid):
SQL> col mnth noprint
SQL> select trunc(hiredate,'month') mnth, 
  2         TO_CHAR(HIREDATE,'yyyy') "YEAR",TO_CHAR(HIREDATE,'Month') "MONTH" ,count(*) total
  3  FROM EMP
  4  GROUP BY trunc(hiredate,'month'), TO_CHAR(HIREDATE,'yyyy') ,TO_CHAR(HIREDATE,'Month')
  5  order by 1
  6  /
YEAR MONTH          TOTAL
---- --------- ----------
1980 December           1
1981 February           2
1981 April              1
1981 May                1
1981 June               1
1981 September          2
1981 November           1
1981 December           2
1982 January            1
1987 April              1
1987 May                1

Note: Avoid the use of YEAR, MONTH, or use them with double-quotes (see my query).
Before using an alias, a column, table... name, query V$RESERVED_WORDS.

Regards
Michel

[Updated on: Thu, 31 January 2013 03:06]

Report message to a moderator

Re: Order by Month [message #576060 is a reply to message #576054] Thu, 31 January 2013 04:09 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi All,

Thanks for reply.In future I'll take care all of your suggestions.
And sorry I am deviating from what I have asked becuase the ename is not fitting the test case.
SELECT job jobs,TO_CHAR(HIREDATE,'yyyy') YEARly,TO_CHAR(HIREDATE,'Month') MONTHly ,count(*) total
FROM EMP
GROUP job,BY TO_CHAR(HIREDATE,'yyyy') ,TO_CHAR(HIREDATE,'Month');

But still If I want to the output Like and not only sql*plus.It should be generalized way.
Jobs Yearly Monthly Total
Manager 1980 January 2
Manager 1981 February 5
and so on.......

Regards,
Nathan

[Updated on: Thu, 31 January 2013 04:10]

Report message to a moderator

Re: Order by Month [message #576063 is a reply to message #576060] Thu, 31 January 2013 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are not using SQL*Plus then you can use the same query but not display the "order by" column: this is your program you can choose what you want to display.

Regards
Michel
Re: Order by Month [message #576069 is a reply to message #576063] Thu, 31 January 2013 05:07 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

I think it's working..
SELECT jobs,yearly,to_char(to_date('01/'||monthly||'/2013','DD/MM/YYYY'),'Month')monthly,total FROM(
SELECT JOB jobs,to_char(creation_date,'yyyy') yearly,to_char(creation_date,'MM') monthly,count(*) total
FROM XXX
GROUP BY JOB,to_char(creation_date,'yyyy') ,to_char(creation_date,'MM') 
ORDER BY 1,2,3);


Regards,
Nathan



[Edit MC: table name hidden at OP's request]

[Updated on: Thu, 31 January 2013 07:24] by Moderator

Report message to a moderator

Re: Order by Month [message #576071 is a reply to message #576069] Thu, 31 January 2013 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nobody can know, it would better if you:
1/ Use the same tables than other people in the topic
2/ Correctly format the query, if you don't know use a SQL Formatter
3/ Test your query before posting it.

Posting a query like that no one can even see if it is related to the question is just useless.

Regards
Michel

[Updated on: Thu, 31 January 2013 05:33]

Report message to a moderator

Re: Order by Month [message #576075 is a reply to message #576071] Thu, 31 January 2013 06:07 Go to previous message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Sorry Michel for not providing proper result.

SELECT jobs,
  yearly,
  TO_CHAR(to_date('01/'
  ||monthly
  ||'/2013','DD/MM/YYYY'),'Month')monthly,
  total
FROM
  (SELECT JOB jobs,
    TO_CHAR(hiredate,'yyyy') yearly,
    TO_CHAR(hiredate,'MM') monthly,
    COUNT(*) total
  FROM emp
  GROUP BY JOB,
    TO_CHAR(hiredate,'yyyy') ,
    TO_CHAR(hiredate,'MM')
  ORDER BY 1,2,3
  );


Regards,
Nathan
Previous Topic: Goto option not working in EXCEPTION
Next Topic: Need help in creating a job.
Goto Forum:
  


Current Time: Fri Oct 24 21:51:47 CDT 2014

Total time taken to generate the page: 0.11116 seconds