Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

showing all months using outer join

zulfffiJun 10 2010 — edited Jun 11 2010
Hi,

I am using emp table example to describe my problem here,

I made a query which is group by hiredate month and showing the sum of the salary ,

as follows,

select
to_date(to_char(hiredate,'MON-RRRR'),'MON-RRRR'), sum(sal)
from
emp
Group by
to_date(to_char(hiredate,'MON-RRRR'),'MON-RRRR')
SQL> /

TO_DATE(T SUM(SAL)
--------- ----------
01-FEB-10 21000
01-MAR-10 30000
01-MAY-10 43000
01-JUN-10 70000

what i want is to also show those months too where no hiring done
like
month sum(sal)
01-Jan-10 0
01-FEB-10 21000
01-MAR-10 30000
01-Apr-10 0
01-MAY-10 43000
01-Jun-10 0
01-JUN-10 70000
01-Aug-10 0
01-Sep-10 0
01-Oct-10 0
01-Nov-10 0
01-Dec-10 0

I tried by making another table with months entered AND use outer join but failed to get the result

any idea will be appreciated
This post has been answered by Nimish Garg on Jun 10 2010
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 9 2010
Added on Jun 10 2010
13 comments
5,186 views