CODEWITHSUNDEEP
sqloracle-databasecount
Rob Morris
Rob Morris

Reputation: 137

Oracle SQL - Count number of jobs per month

I'm looking to count the total number of time a type of job (G012) has appeared each month with an status_code of '5200' in each area.

The code I have so far is:

select area.area_name, count(area.area_name)
from area
inner join central_site on central_site.area_code = area.area_code
inner join job on job.site_code = central_site.site_code
inner join job_type on job.job_type_key = job_type.job_type_key
inner join job_status_log on job.job_number = job_status_log.job_number
where job_type.job_type_key = 'G012' and job_status_log.status_code = '5200'
group by area.area_name
order by area.area_name

I'm not sure how to get this to count so that each month is in a different category - but what I would like to end up with is something like:

Area             April     May     June    July    August    September    October
North Ward         2        5        3       2        7         4            3
South Ward         2        3        4       4        6         4            4

Upvotes: 0

Views: 179

Answers (1)

Radagast81
Radagast81

Reputation: 3016

Basically you have to group the data also by the month:

select area.area_name,  EXTRACT(MONTH FROM job.actual_start_date) mon,  count(area.area_name)
from area
inner join central_site on central_site.area_code = area.area_code
inner join job on job.site_code = central_site.site_code
inner join job_type_key on job.job_type_key = job_type.job_type_key
inner join job_status_log on job.job_number = job_status_log.job_number
where job_type.job_type_key = 'G012' and job_status_log.status_code = '5200'
group by area.area_name,  EXTRACT(MONTH FROM job.actual_start_date)
order by area.area_name

Instead of EXTRACT(MONTH FROM job.actual_start_date) you could also use TO_CHAR(job.actual_start_date, 'Month') for the name instead of the number.

To get your desired output (month as columns instead of rows) you can use pivot:

SELECT *
  FROM (select area.area_name,  EXTRACT(MONTH FROM job.actual_start_date) mon
          from area
         inner join central_site on central_site.area_code = area.area_code
         inner join job on job.site_code = central_site.site_code
         inner join job_type_key on job.job_type_key = job_type.job_type_key
         inner join job_status_log on job.job_number = job_status_log.job_number
         where job_type.job_type_key = 'G012' and job_status_log.status_code = '5200')
PIVOT (
  COUNT(*)
  FOR mon IN (1 AS January, 2 AS February, 3 AS March, 4 AS April, 5 AS May, 6 AS June, 7 AS July, 8 AS August, 9 AS September, 10 AS October, 11 AS November, 12 AS December)
)

To separate the data also by year, just add a EXTRACT(YEAR FROM job.actual_start_date) column everywhere there is EXTRACT(MONTH FROM job.actual_start_date) now.

Upvotes: 1

Related Questions