Count Query with Months and Year

[mozart]

I am trying to write a query that will give me a month count of jobs completed per location.

I have a Locations Table, Workorder Table.

The Jobs table whole the unique workorder details and a date stamp.

Now some locations over a year may not have a workorder completed in every month. However I do not want these locations excluded

I have managed to write an outer join query which captures every location regardless whether a job has been done or not however I cannot get the results to show the months where no job has been done for that month. If i try a second outer join its says I cannot have two outer joins.

I am open to suggestions or any other approach I could consider.

My current query is below

SELECT A.LOCATION,A.DESCRIPTION,A.REGION,B.MONTH,B.COUNT FROM
  (SELECT LOCATIONS.LOCATION"LOCATION",LOCATIONS.DESCRIPTION"DESCRIPTION",LOCATIONS.LO1"REGION" FROM LOCATIONS)A,
    (SELECT WORKORDER.LOCATION "LOCATION",LOCATIONS.DESCRIPTION"DESCRIPTION",LOCATIONS.LO1"REGION",TO_CHAR(WORKORDER.REPORTDATE,'MON-YYYY')"MONTH",COUNT(WORKORDER.WONUM)"COUNT"
         FROM WORKORDER,LOCATIONS WHERE WORKORDER.LOCATION=LOCATIONS.LOCATION
             AND WORKORDER.CAPEX='N'
             AND WORKORDER.STATUS NOT IN ('CAN','ABORT')
             AND  REPORTDATE BETWEEN  (:DATE_BEGIN) AND (:DATE_END +1) 
         GROUP BY WORKORDER.LOCATION,LOCATIONS.DESCRIPTION,LOCATIONS.LO1,TO_CHAR(WORKORDER.REPORTDATE,'MON-YYYY'),TO_CHAR(WORKORDER.REPORTDATE,'YYYY-MM')
          ORDER BY WORKORDER.LOCATION, TO_CHAR(WORKORDER.REPORTDATE,'YYYY-MM'))B
WHERE A.LOCATION=B.LOCATION(+)
ORDER BY B.COUNT 

[tamilselvan]

Try OUTER JOIN.
FROM WORKORDER,LOCATIONS
WHERE WORKORDER.LOCATION(+)=LOCATIONS.LOCATION

Are you getting an error here?

Tamil

[mozart]

Does not do much other than give me a normal join and not returning my locations where there a null value for count. I used the inline query with outer join to counter this but with this i can't get months to also outerjoin and display (in my original posted query).

One way i thought of doing this was generating an inline query that would pair each month with each location and then do the outer join however with over a 1000 locations and 12 months in a year this is not efficient.

Should Look like something like this

Location
1000 Jan-04 1
1000 Feb-04 5
1000 Mar-04 0
.............................
.............................

1001 Jan-04 2